SQL 動態條件查詢：從字串拼湊到參數化邏輯的優雅轉型

痛點：傳統字串拼湊的維護地獄

多條件查詢常見寫法：C# 端用 StringBuilder 依條件組裝 WHERE 子句，再拼接 SELECT。缺點顯而易見：程式碼冗長、易生 SQL 注入、參數化困難、計畫快取利用率低、除錯困難。

// 範例一：傳統拼湊 (略)

解法一：純 SQL 邏輯判斷 (短路特性)

利用 ('@param = '' OR Col = @param') 模式，參數為空時條件恆真自動略過，非空時套用篩選。SQL Server 查詢優化器能識別此模式並產生參數化計畫：

DECLARE @Order VARCHAR(20) = '', @Produce VARCHAR(20) = '711',
        @StartDate DATETIME = NULL, @EndDate DATETIME = NULL;

SELECT * FROM Sales.SalesOrderDetail
WHERE (@Order = '' OR SalesOrderID = @Order)
  AND (@Produce = '' OR ProductID = @Produce)
  AND (@StartDate IS NULL OR ModifiedDate > @StartDate)
  AND (@EndDate IS NULL OR ModifiedDate < @EndDate);

C# 呼叫端只需傳入參數，無需組裝 SQL 字串：

string sql = @"
SELECT * FROM Sales.SalesOrderDetail
WHERE (@Order = '' OR SalesOrderID = @Order)
  AND (@Produce = '' OR ProductID = @Produce)
  AND (@StartDate IS NULL OR ModifiedDate > @StartDate)
  AND (@EndDate IS NULL OR ModifiedDate < @EndDate)";

using (var cmd = new SqlCommand(sql, conn)) {
    cmd.Parameters.AddWithValue("@Order", txtOrder.Text);
    cmd.Parameters.AddWithValue("@Produce", txtProduce.Text);
    cmd.Parameters.AddWithValue("@StartDate", string.IsNullOrEmpty(txtStart.Text) ? (object)DBNull.Value : txtStart.Text);
    cmd.Parameters.AddWithValue("@EndDate", string.IsNullOrEmpty(txtEnd.Text) ? (object)DBNull.Value : txtEnd.Text);
    // ExecuteReader...
}

解法二：SqlDataSource 控制項宣告式繫結

ASP.NET WebForms 可直接在標記中定義參數化查詢，配合 ConvertEmptyStringToNull="true" 將空字串轉為 NULL：

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    CancelSelectOnNullParameter="false"
    SelectCommand="
        SELECT * FROM Sales.SalesOrderDetail
        WHERE (@Order IS NULL OR SalesOrderID = @Order)
          AND (@Produce IS NULL OR ProductID = @Produce)
          AND (@StartDate IS NULL OR ModifiedDate > @StartDate)
          AND (@EndDate IS NULL OR ModifiedDate < @EndDate)">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtOrder" Name="Order" 
            ConvertEmptyStringToNull="true" />
        <asp:ControlParameter ControlID="txtProduce" Name="Produce" 
            ConvertEmptyStringToNull="true" />
        <asp:ControlParameter ControlID="txtStartDate" Name="StartDate" Type="DateTime"
            ConvertEmptyStringToNull="true" />
        <asp:ControlParameter ControlID="txtEndDate" Name="EndDate" Type="DateTime"
            ConvertEmptyStringToNull="true" />
    </SelectParameters>
</asp:SqlDataSource>

解法三：預存程序封裝 (最佳封裝與計畫重用)

CREATE PROCEDURE dbo.SearchSalesOrderDetail
    @Order VARCHAR(20) = NULL,
    @Produce VARCHAR(20) = NULL,
    @StartDate DATETIME = NULL,
    @EndDate DATETIME = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Sales.SalesOrderDetail
    WHERE (@Order IS NULL OR SalesOrderID = @Order)
      AND (@Produce IS NULL OR ProductID = @Produce)
      AND (@StartDate IS NULL OR ModifiedDate > @StartDate)
      AND (@EndDate IS NULL OR ModifiedDate < @EndDate);
END

優點：邏輯集中資料庫端、參數強型別、計畫快取穩定、權限可控、易於效能調校 (OPTION RECOMPILE 等)。

效能考量與優化策略

作者實測指出此模式在大資料量下效能較差，主要原因：

• 優化器難以針對特定參數組合產生最佳計畫 (參數嗅探問題)。
• 可能導致索引掃描而非尋找。

改善方向：

1. 動態 SQL + sp_executesql：依實際傳入參數動態組裝 WHERE，僅包含非空條件，產生專用計畫 (需防注入)。
2. OPTION (RECOMPILE)：在預存程序/查詢尾加上 OPTION (RECOMPILE)，每次編譯針對當前參數值優化 (適用中低頻查詢)。
3. 優化提示：針對高選擇性條件加入 FORCESEEK 或覆蓋索引。
4. 分頁查詢：配合 OFFSET/FETCH (SQL 2012+) 避免全量傳回。

總結

「參數化短路邏輯」是解決動態條件查詢的標準模式，兼顧安全性、可維護性與計畫重用。針對效能敏感場景，可進階採用動態 SQL、重編譯提示或索引優化。核心原則：拒絕字串拼湊，擁抱參數化，邏輯下推資料庫層。