[SQL] 使用 Stored Procedure 動態組成 SQL 查詢指令

以下示範一個可以動態組成查詢子句的 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 @statement

 RETURN

在上例中，任一參數的值如果不是 NULL，就會被加上像 [Deleted] = 1 之類的鈙述到 WHERE 子句之中，依此類推。如果一參數不提供值（也就是 NULL），則代表該欄位不納入 WHERE 的查詢條件裡面。如果全部參數都沒有值，那麼這個 WHERE 子句會變成 WHERE 1=1，也就是不限制條件，全部的資料都傳回來的意思。依這種方式組成的 SQL 查詢子句，就可以達到最大的彈性。

如果你想知道如何在 ASP.NET 網頁中如何描配這個 Stored Procedure 進行資料查詢，請參考「[ADO.NET] 動態 SQL 指令與 SqlDataSource 的 QueryStringParameter 配合查詢」這一篇。