您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

動態 SQL 與條件式 WHERE 讓預存程序擁有拼接 SQL 同等彈性

編輯：關於SqlServer

問題背景

傳統 ASP 時代習慣在程式碼拼接 SQL，ASP.NET 推薦用預存程序，但預存程序如何支援「使用者可選擇性填寫查詢條件」？

資料表結構


CREATE TABLE t_student (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    age INT,
    address NVARCHAR(50)
);
INSERT INTO t_student VALUES (1, N'tom', 12, N'北京市海澱區');
INSERT INTO t_student VALUES (2, N'jim', 40, N'上海');
INSERT INTO t_student VALUES (3, N'roles', 10, N'beijing');

方案一：動態 SQL (sp_executesql) — 最彈性


CREATE PROC usp_SearchStudent
    @id INT = NULL,
    @name NVARCHAR(50) = NULL,
    @age INT = NULL,
    @address NVARCHAR(50) = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM t_student WHERE 1=1';
    DECLARE @params NVARCHAR(MAX) = N'@id INT, @name NVARCHAR(50), @age INT, @address NVARCHAR(50)';

    IF @id IS NOT NULL      SET @sql += N' AND id = @id';
    IF @name IS NOT NULL    SET @sql += N' AND name = @name';
    IF @age IS NOT NULL     SET @sql += N' AND age = @age';
    IF @address IS NOT NULL SET @sql += N' AND address = @address';

    EXEC sp_executesql @sql, @params, @id, @name, @age, @address;
END
GO

方案二：靜態 SQL + IS NULL/COALESCE — 免動態編譯


CREATE PROC usp_SearchStudent_Static
    @id INT = NULL,
    @name NVARCHAR(50) = NULL,
    @age INT = NULL,
    @address NVARCHAR(50) = NULL
AS
BEGIN
    SELECT * FROM t_student
    WHERE (id = @id OR @id IS NULL)
      AND (name = @name OR @name IS NULL)
      AND (age = @age OR @age IS NULL)
      AND (address = @address OR @address IS NULL);
END
GO

方案三：OPTION (RECOMPILE) — 參數嗅探優化


-- 在方案二結尾加上
OPTION (RECOMPILE);

強制每次重新編譯，依實際參數值產生最佳計畫，避免「參數嗅探」導致計畫不穩。

效能與安全性比較

方案	計畫重用	注入風險	適用場景
動態 SQL	參數化後可重用	低 (參數化)	條件組合極多、需動態 ORDER BY/TOP
靜態 SQL + OR	單一計畫	無	條件固定、參數較少
靜態 + RECOMPILE	不重用	無	資料分布嚴重傾斜、需最佳計畫

C# 呼叫範例


using (var cmd = new SqlCommand("usp_SearchStudent", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.AddWithValue("@id", (object)txtId.Text ?? DBNull.Value);
    cmd.Parameters.AddWithValue("@name", (object)txtName.Text ?? DBNull.Value);
    // ...
    using (var reader = cmd.ExecuteReader()) { /* ... */ }
}

掌握這三種模式，預存程序即能兼具安全、效能與彈性，完美取代字串拼接。