預存程序參數靈活運用是資料庫程式設計核心技能。本文系統講解三類參數模式、動態 WHERE 條件構建的兩大流派 (動態 SQL vs 條件邏輯)、防 SQL 注入強化模式,並給出生產級程式碼範本。
CREATE PROCEDURE dbo.usp_ParamDemo
@InParam INT, -- 僅輸入
@OutParam INT OUTPUT, -- 僅輸出
@InOutParam INT OUTPUT -- 雙向 (INPUT + OUTPUT)
AS
BEGIN
SET @OutParam = @InParam + @InOutParam; -- 1 + 2 = 3
SET @InOutParam = @OutParam + 1; -- 3 + 1 = 4
END
-- 呼叫端
DECLARE @in INT = 1, @out INT, @inout INT = 2;
EXEC dbo.usp_ParamDemo @in, @out OUTPUT, @inout OUTPUT;
SELECT @in AS InVal, @out AS OutVal, @inout AS InOutVal; -- 1, 3, 4
關鍵:呼叫時 `OUTPUT` 關鍵字**雙向聲明** (定義處 + 呼叫處),否則輸出值不回傳。
CREATE PROCEDURE dbo.usp_DynamicSearch
@Name NVARCHAR(50) = NULL,
@Age INT = NULL,
@IDs NVARCHAR(200) = NULL -- 逗號分隔 ID 列表
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM #TestTable WHERE 1=1';
DECLARE @params NVARCHAR(MAX) = N'@Name NVARCHAR(50), @Age INT, @IDs NVARCHAR(200)';
IF @Name IS NOT NULL
SET @sql += N' AND Name LIKE @Name + N''%''';
IF @Age IS NOT NULL
SET @sql += N' AND Age = @Age';
IF @IDs IS NOT NULL
BEGIN
-- 安全處理 IN 列表:字串分割轉表值參數 (TVP) 或 STRING_SPLIT (2016+)
SET @sql += N' AND ID IN (SELECT value FROM STRING_SPLIT(@IDs, '',''))';
END
EXEC sp_executesql @sql, @params, @Name, @Age, @IDs;
END
CREATE PROCEDURE dbo.usp_StaticSearch
@Name NVARCHAR(50) = NULL,
@Age INT = NULL,
@IDs NVARCHAR(200) = NULL
AS
BEGIN
SELECT * FROM #TestTable
WHERE 1=1
AND (@Name IS NULL OR Name LIKE @Name + N'%')
AND (@Age IS NULL OR Age = @Age)
AND (@IDs IS NULL OR ID IN (SELECT value FROM STRING_SPLIT(@IDs, ',')));
END
| 維度 | 動態 SQL | 靜態條件邏輯 |
|---|---|---|
| 查詢計畫品質 | 針對實際參數生成最優計畫 | 通用計畫,可能非最優 (參數嗅探) |
| 計畫快取壓力 | 高 (每種參數組合一份計畫) | 低 (單一計畫重用) |
| 注入風險 | 需嚴格參數化 (`sp_executesql` + 參數) | 原生免疫 |
| 維護複雜度 | 字串拼接易出錯,除錯困難 | 結構清晰,易閱讀修改 |
| 適用場景 | 高選擇性、複雜可選 JOIN、跨表動態 | 簡單可選 WHERE、高頻呼叫、OLTP 核心 |