SQL Server 存儲過程動態參數構建實戰：動態 SQL 安全拼接與可選條件查詢

存儲過程常需根據傳入參數動態構建 WHERE 條件 (如：用戶名、年齡、ID 列表任意組合)。本文對比「CASE WHEN 硬編碼」與「動態 SQL 參數化」兩種方案，深度剖析性能、維護性、安全性差異，給出生產級動態構建模板。

場景：可選參數查詢

CREATE TABLE #Test (Id INT IDENTITY, Name VARCHAR(20), Age INT);
INSERT #Test VALUES ('小明',23),('小紅',28),('小軍',27);
-- 需求：@Name、@Age、@Ids 三參數任意組合過濾

❌ 方案一：CASE WHEN 硬編碼 WHERE (不推薦)

SELECT * FROM #Test WHERE 1=1
    AND (@Name IS NULL OR Name LIKE @Name + '%')
    AND (@Age IS NULL OR Age = @Age)
    AND (@Ids IS NULL OR Id IN (SELECT value FROM STRING_SPLIT(@Ids, ',')));

致命缺陷：

• 參數嗅探災難：首次編譯參數值決定計劃，後續不同參數組合導致計劃極度不優 (如首次無參數 → 全表掃描計劃，後續帶參數仍走掃描)。
• 索引無法求值：OR 條件導致優化器放棄索引求值，轉為掃描。
• 維護地獄：條件超過 5-6 個時可讀性崩潰，修改極易引入 Bug。

✅ 方案二：動態 SQL + sp_executesql 參數化 (推薦)

CREATE PROCEDURE usp_DynamicSearch
    @Name VARCHAR(20) = NULL,
    @Age INT = NULL,
    @Ids VARCHAR(MAX) = NULL  -- 逗號分隔: '1,3,5'
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM #Test WHERE 1=1';
    DECLARE @Params NVARCHAR(MAX) = N'@Name VARCHAR(20), @Age INT, @Ids VARCHAR(MAX)';
    
    -- 動態拼接條件 (僅拼接列名/運算符，值通過參數傳遞)
    IF @Name IS NOT NULL
        SET @SQL += N' AND Name LIKE @Name + ''%''';
    
    IF @Age IS NOT NULL
        SET @SQL += N' AND Age = @Age';
    
    IF @Ids IS NOT NULL
    BEGIN
        -- 安全構建 IN 列表：將字符串轉為表值參數或使用 STRING_SPLIT (2016+)
        -- 此處演示參數化拼接 (需確保 @Ids 無注入風險，建議應用層驗證純數字)
        SET @SQL += N' AND Id IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@Ids, '',''))';
        -- 2016 前需自定義分裂函數或 XML 解析
    END
    
    -- 執行參數化動態 SQL
    EXEC sp_executesql @SQL, @Params, @Name, @Age, @Ids;
END;
GO

-- 測試調用
EXEC usp_DynamicSearch @Name = '小明', @Age = 23;
EXEC usp_DynamicSearch @Ids = '1,3';
EXEC usp_DynamicSearch; -- 無參數全表

進階：多條件 AND 組合模板 (可直接復用)

DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM dbo.Orders WHERE 1=1';
DECLARE @Params NVARCHAR(MAX) = N'@SellerNick NVARCHAR(100), @LogisticsId INT, @Status TINYINT';

IF @SellerNick IS NOT NULL
    SET @SQL += N' AND SellerNick = @SellerNick';

IF @LogisticsId IS NOT NULL
    SET @SQL += N' AND LogisticsId = @LogisticsId';

IF @Status IS NOT NULL
    SET @SQL += N' AND Status = @Status';

-- 更多條件按相同模式追加...

EXEC sp_executesql @SQL, @Params, @SellerNick, @LogisticsId, @Status;

動態 SQL 安全鐵律

1. 值永不拼接：所有用戶輸入值通過 sp_executesql 參數傳遞，絕不 字符串拼接入 SQL。
2. 標識符白名單：動態表名/列名/排序列必須通過白名單驗證，或用 QUOTENAME() 包裹。
   IF @OrderCol IN ('Id','Name','Date') SET @SQL += ' ORDER BY ' + QUOTENAME(@OrderCol);
3. IN 列表參數化：STRING_SPLIT (2016+)、表值參數 (TVP)、XML/JSON 解析，禁用字符串拼接。
4. 計劃復用：相同 @SQL 結構復用計劃，參數值變化不觸發重編譯。
5. 調試友好：PRINT @SQL 輸出最終語句，復制 SSMS 直接運行驗證。

性能優化提示

• OPTION (RECOMPILE)：極少數「參數值分佈極不均勻、計劃差異巨大」場景可在動態 SQL 末尾添加，強制每次重編譯生成最優計劃 (犧牲 CPU 換 I/O)。
• 覆蓋索引：針對高頻查詢組合建立 INCLUDE 索引，消除 Key Lookup。
• 緩存總數：分頁場景總數單獨緩存 1-5 分鐘，避免重複 COUNT(*)。

總結

動態參數查詢 = 動態 SQL 字符串拼接結構 (列名/運算符/邏輯) + sp_executesql 參數化傳值。這是唯一兼顧「防注入、計劃復用、索引求值、可維護性」的正解。將上述模板封裝為團隊代碼片段，統一代碼風格，杜絕 CASE WHEN 硬編碼反模式。