SQL Server 預存程序多條件查詢：拒絕動態 SQL 拼接的三種優雅寫法

為什麼要避免動態 SQL 拼接？

在預存程序中動態拼接 SQL 字串雖然靈活，但帶來嚴重副作用：

• 效能受損：無法重用執行計畫，每次都需重新編譯
• 安全風險：若參數處理不當，易遭 SQL 注入攻擊
• 維護困難：百行以上的動態 SQL 除錯極其痛苦
• 除錯受限：無法在 SSMS 中直接設定中斷點追蹤

方案一：IF-ELSE 分支明確列舉（適用於條件較少）

IF @addDate IS NOT NULL AND @name <> ''
    SELECT * FROM [table] WHERE addDate = @addDate AND name = @name;
ELSE IF @addDate IS NOT NULL AND @name = ''
    SELECT * FROM [table] WHERE addDate = @addDate;
ELSE IF @addDate IS NULL AND @name <> ''
    SELECT * FROM [table] WHERE name = @name;
ELSE
    SELECT * FROM [table];

優點：邏輯清晰、執行計畫最佳化；缺點：條件組合爆炸時程式碼冗長。

方案二：WHERE 條件短路邏輯（推薦通用寫法）

SELECT * FROM [table]
WHERE (addDate = @addDate OR @addDate IS NULL)
  AND (name = @name OR @name = '');

核心原理：利用 OR @param IS NULL 使條件在參數為 NULL 時自動失效。
注意：字串參數建議用 '' 而非 NULL，避免 NULL = '' 判斷差異。

方案三：CASE 運算式動態比對（進階寫法）

SELECT * FROM [table]
WHERE addDate = CASE WHEN @addDate IS NULL THEN addDate ELSE @addDate END
  AND name = CASE WHEN @name = '' THEN name ELSE @name END;

優勢：語意明確表達「若無輸入則忽略此欄位」；
效能提示：SQL Server 最佳化器通常能將其轉換為方案二的等效計畫。

效能與適用場景對比

實戰建議

1. 優先採用方案二（短路邏輯），平衡效能與可維護性
2. 為常用查詢欄位建立覆蓋索引，避免掃描
3. 使用 OPTION (RECOMPILE) 處理參數嗅探問題（SQL 2008+）
4. 避免在 WHERE 中對欄位使用函數（如 ISNULL(addDate, '') = @addDate），導致索引失效

掌握這三種模式，即可在不犧牲效能與安全性的前提下，優雅解決預存程序的多條件動態查詢需求。