SQL 多條件組合查詢高效建模：控制參數 + 短路邏輯，避免動態 SQL 拼接

業務痛點

使用者介面提供 10+ 查詢條件 (單號、日期範圍、類別、狀態、關鍵字...)，任意組合。傳統做法：

• ❌ 應用層拼接 SQL 字串 → 難維護、防注入弱、計畫無法重用、可讀性差。
• ❌ 單一預存程序帶大量 IF @param IS NOT NULL 分支 → 參數嗅探導致計畫不穩。

核心模式：控制參數 + 邏輯短路

在 WHERE 子句中為每組條件設計控制旗標，利用 SQL 布林邏輯短路特性，旗標為 0 時直接滿足條件，跳過後續昂貴運算。

範本結構

WHERE 
  -- 條件組 1：單據狀態
  (@Ctrl1 = 0 OR (@Ctrl1 = 1 AND DocStatus NOT IN (110,120,130)))

  -- 條件組 2：日期範圍 (多種日期欄位)
  AND (@Ctrl2 = 0 OR 
       (@Ctrl2 = 1 AND DocDate BETWEEN @DateB AND @DateE) OR
       (@Ctrl2 = 2 AND DocNo IN (SELECT DocNo FROM SubTable1 WHERE SubDate BETWEEN @DateB AND @DateE)) OR
       (@Ctrl2 = 3 AND DocNo IN (SELECT DocNo FROM SubTable2 WHERE RecDate BETWEEN @DateB AND @DateE)))

  -- 條件組 3：單據類型
  AND (@Ctrl3 = 0 OR (@Ctrl3 = 1 AND DocType = @DocType))

  -- 條件組 4：業務種類 (多欄位 OR)
  AND (@Ctrl4 = 0 OR (@Ctrl4 = 1 AND (SvcType1 = @SvcType OR SvcType2 = @SvcType OR SvcType3 = @SvcType)))

  -- 條件組 5：狀態多選 (IN 清單)
  AND (0 IN (@Ctrl5) OR 
       (1 IN (@Ctrl5) AND DocStatus IN (20)) OR
       (2 IN (@Ctrl5) AND DocStatus IN (40,60)) OR
       (3 IN (@Ctrl5) AND DocStatus IN (80)) ...)

  -- 條條件組 6~N...

關鍵設計要點

1. 控制參數前置：@CtrlX = 0 OR ... 確保短路在最左側，旗標關閉時零成本。
2. 旗標語意化：0=不啟用、1=啟用模式 A、2=啟用模式 B... 避免魔術數字。
3. 參數傳遞：
   　• 單值：@Ctrl1, @Ctrl2...
   　• 陣列/表值參數 (TVP)：@CtrlArray TABLE (Id INT) → (1 IN (SELECT Id FROM @CtrlArray) AND ...)
   　• 字串編碼：@CtrlStr = 'YNNYY' → SUBSTRING(@CtrlStr, 1, 1) = 'Y' / CHARINDEX('A', @CtrlStr) > 0
4. 索引友善：條件欄位建立適當索引；IN (SELECT...) 子查詢確保子查詢可 SARGable (有索引、無函數)。
5. 計畫穩定：單一預存程序/參數化查詢，計畫快取重用，避免動態 SQL 產生大量計畫。

實際執行計畫驗證

當 @Ctrl1=0 時，優化器識別 0=0 恆真，完全移除 後續條件的查詢運算子 (Constant Scan / Filter)，不產生任何 I/O。

進階：PowerBuilder DataWindow 陣列傳遞

-- DW 查詢條件
WHERE (1 IN (:ctrl_array) AND Status = :status)
-- 陣列值：ctrl_array = {1,0,0,0,5,9} 表示第 1、5、6 組條件啟用

PB 原生支援陣列綁定，比字串解析更高效、型別安全。

對比：動態 SQL 拼接缺點

• 計畫快取膨脹 (每種組合一個計畫)。
• SQL 注入風險 (參數化困難)。
• 除錯困難 (需 Profile 捕捉實際 SQL)。
• 邏輯與資料混雜，違反介面隔離原則。

結語

「控制參數 + 短路邏輯」是多條件組合查詢的最佳平衡點：單一查詢結構、計畫穩定、零成本條件關閉、參數化安全、易於單元測試。適用於報表查詢、清單篩選、API 參數化查詢等高頻場景。