萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 預存程序參數進階:輸入/輸出/雙向參數、動態條件構建與防注入模式

預存程序參數靈活運用是資料庫程式設計核心技能。本文系統講解三類參數模式、動態 WHERE 條件構建的兩大流派 (動態 SQL vs 條件邏輯)、防 SQL 注入強化模式,並給出生產級程式碼範本。

1. 參數模式完整示範

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` 關鍵字**雙向聲明** (定義處 + 呼叫處),否則輸出值不回傳。

2. 動態條件構建:兩大流派 PK

流派 A:動態 SQL (靈活、計畫最優、需防注入)

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

流派 B:靜態 SQL + 條件邏輯 (計畫重用、無注入風險、可能掃描較多)

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

3. 選型決策矩陣

4. 防注入鐵律 (動態 SQL 必守)

  1. **永遠使用 `sp_executesql` + 參數化**,絕不直接拼接使用者輸入
  2. 識別碼 (表名/欄位名) 需動態時,使用 `QUOTENAME()` 並驗證白名單
  3. `IN` 列表改用 `STRING_SPLIT` / `OPENJSON` / 自訂 TVP,勿拼接字串
  4. 最小權限:執行者僅需 `EXECUTE` 權限,無需底表 `SELECT` 權限 (所有權鏈)

5. 進階模式:表值參數 (TVP) 傳遞複雜清單

-- 1. 建立使用者定義表類型
CREATE TYPE dbo.IdList AS TABLE (ID INT PRIMARY KEY);

-- 2. 程序接收 TVP
CREATE PROCEDURE usp_TvpSearch @Ids dbo.IdList READONLY
AS
SELECT t.* FROM dbo.BigTable t JOIN @Ids i ON t.ID = i.ID;

-- 3. 呼叫端 (C# / ADO.NET)
var tvp = new DataTable(); tvp.Columns.Add("ID", typeof(int));
foreach (var id in ids) tvp.Rows.Add(id);
cmd.Parameters.AddWithValue("@Ids", tvp); // SqlDbType.Structured

掌握參數模式、動態/靜態條件構建取捨、防注入鐵律與 TVP 進階用法,即能寫出安全、高效、可維護的企業級預存程序。

copyright © 萬盛學電腦網 all rights reserved
維度動態 SQL靜態條件邏輯
查詢計畫品質針對實際參數生成最優計畫通用計畫,可能非最優 (參數嗅探)
計畫快取壓力高 (每種參數組合一份計畫)低 (單一計畫重用)
注入風險需嚴格參數化 (`sp_executesql` + 參數)原生免疫
維護複雜度字串拼接易出錯,除錯困難結構清晰,易閱讀修改
適用場景高選擇性、複雜可選 JOIN、跨表動態簡單可選 WHERE、高頻呼叫、OLTP 核心