以下示範一個可以動態組成查詢子句的 SQL 指令, 以 MS SQL 的 Stored Procedure 寫成。其中, 如果任一個參數(或全部)傳入 NULL 值, 即代表不限制那個欄位的條件...
以下示範一個可以動態組成查詢子句的 SQL 指令, 以 MS SQL 的 Stored Procedure 寫成。其中, 如果任一個參數(或全部)傳入 NULL 值, 即代表不限制那個欄位的條件。
CREATE PROCEDURE dbo.QueryProduct
(
@ProductID smallint,
@Time datetime,
@Span smallint,
@Deleted bit,
@Status tinyint,
@Title nvarchar(255)
)
AS
DECLARE @statement nvarchar(256)
DECLARE @orderby nvarchar(40)
DECLARE @where nvarchar(256)
DECLARE @wherespan varchar(3)
SET @statement = 'SELECT TOP 100 [ID], [ProductID], [Time], [Title], [Status], [Deleted] FROM [tblProducts] '
SET @orderby = ' ORDER BY [ProductID], [Time] '
/* ------------------ WHERE [ProductID] ----------------------------------------------------- */
IF (@ProductID IS NULL)
BEGIN
SET @where = ' WHERE 1 = 1 '
END
ELSE
BEGIN
SET @where = ' WHERE [ProductID] = ' + CAST(@ProductID AS varchar)
END/* ------------------ WHERE [Time] -------------------------------------------------------- */
IF (@Time IS NOT NULL)
BEGIN
SET @wherespan = CAST(ISNULL(@Span, 1) AS varchar)
SET @where = @where + ' AND (ABS(DATEDIFF(day, ''' + CAST(@Time AS varchar) + ''', [Time])) < ' + @wherespan + ')'
END
/* ------------------ WHERE [Deleted] ----------------------------------------------------- */IF (@Deleted IS NOT NULL)
BEGIN
IF (@Deleted = 1)
BEGIN
SET @where = @where + ' AND [Deleted] = 1 '
END
IF (@Deleted = 0)
BEGIN
SET @where = @where + ' AND [Deleted] = 0 '
END
END
/* ------------------ WHERE [Status] ------------------------------------------------------ */
IF (@Status IS NOT NULL)
BEGIN
SET @where = @where + ' AND [Status] = ' + CAST(@Status AS varchar)
END
/* ------------------ WHERE [Title] ------------------------------------------------------- */
IF (@Title IS NOT NULL)
BEGIN
SET @where = @where + ' AND [Title] LIKE ''%' + @Title + '%'''
END/* ------------------ WHERE Clause Composition and Execution ------------------------------ */
SET @statement = @statement + @where + @orderby
PRINT 'STATEMENT: ' + @statement
EXEC sp_executesql @statementRETURN
在上例中,任一參數的值如果不是 NULL,就會被加上像 [Deleted] = 1 之類的鈙述到 WHERE 子句之中,依此類推。如果一參數不提供值(也就是 NULL),則代表該欄位不納入 WHERE 的查詢條件裡面。如果全部參數都沒有值,那麼這個 WHERE 子句會變成 WHERE 1=1,也就是不限制條件,全部的資料都傳回來的意思。依這種方式組成的 SQL 查詢子句,就可以達到最大的彈性。
如果你想知道如何在 ASP.NET 網頁中如何描配這個 Stored Procedure 進行資料查詢,請參考「[ADO.NET] 動態 SQL 指令與 SqlDataSource 的 QueryStringParameter 配合查詢」這一篇。