SQL Server 避免動態 SQL 實現 IN 子句多值參數查詢：XML、字符串分割與表值參數三大方案

存儲過程接收逗號分隔字符串（如 `'3,6,9'`）作為 `IN` 子句參數，是極常見的需求。直接拼接動態 SQL 雖能工作，但引入注入風險、計劃緩存污染、權限提升、調試困難等嚴重問題。本文提供三種**零動態 SQL**的標準化解決方案。

問題復現：為什麼直接用變量失敗？

DECLARE @ManagerIDs VARCHAR(100) = '3,6';
SELECT * FROM HumanResources.Employee WHERE ManagerID IN (@ManagerIDs);
-- Msg 245: Conversion failed when converting '3,6' to int.

原因：`ManagerID` 是 `INT`，SQL Server 嘗試將整個字符串 `'3,6'` 隱式轉換為 `INT` 而非拆分為集合。

方案一：XML 解析（SQL 2005+ 原生支持）

核心邏輯

1. 將逗號字符串包裝為 XML：`'36'`；
2. 使用 XQuery `.nodes()` 方法撕裂為行集；
3. 聯接主表查詢。

完整示例

CREATE PROCEDURE dbo.GetEmployeesByManagerIDs
    @ManagerIDs VARCHAR(MAX)  -- 逗號分隔字符串
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 字符串轉 XML
    DECLARE @xml XML = CAST('' + REPLACE(@ManagerIDs, ',', '') + '' AS XML);

    -- 2. XML 撕裂 + 查詢
    SELECT e.*
    FROM HumanResources.Employee e
    INNER JOIN (
        SELECT T.c.value('.', 'INT') AS ManagerID
        FROM @xml.nodes('/r/v') AS T(c)
    ) m ON e.ManagerID = m.ManagerID;
END;

優缺點

• ✅ 無動態 SQL、防注入、計劃復用；
• ✅ 支持任意數量值、類型安全（XQuery 顯式轉換）；
• ⚠️ XML 解析 CPU 開銷，超大列表（>10 萬值）性能下降；
• ⚠️ 特殊字符（`&` `<` `>`）需預轉義。

方案二：字符串分割函數（通用工具化）

高性能分割函數

CREATE FUNCTION dbo.SplitString_INT
(
    @List VARCHAR(MAX),
    @Delimiter CHAR(1) = ','
)
RETURNS @Result TABLE (Value INT PRIMARY KEY)
AS
BEGIN
    DECLARE @XML XML = CAST('' + REPLACE(@List, @Delimiter, '') + '' AS XML);
    INSERT @Result (Value)
    SELECT T.c.value('.', 'INT') FROM @XML.nodes('/r/v') AS T(c);
    RETURN;
END;

調用示例

SELECT e.*
FROM HumanResources.Employee e
JOIN dbo.SplitString_INT(@ManagerIDs, ',') m ON e.ManagerID = m.Value;

進階：CLR 分割函數（極致性能）

使用 `SqlChars`/`String.Split` 實現，無 XML 開銷，百萬級列表毫秒級返回，需啟用 CLR 集成。

方案三：表值參數 TVP（SQL 2008+ 最佳實踐）

定義表類型

CREATE TYPE dbo.ManagerIDList AS TABLE (ManagerID INT PRIMARY KEY);

存儲過程簽名

CREATE PROCEDURE dbo.GetEmployeesByManagerIDs_TVP
    @ManagerIDs dbo.ManagerIDList READONLY
AS
SELECT e.*
FROM HumanResources.Employee e
JOIN @ManagerIDs m ON e.ManagerID = m.ManagerID;

客戶端調用

• ADO.NET：`SqlParameter.TypeName = "dbo.ManagerIDList"`, `Value = DataTable`/`List`/`IEnumerable`；
• ODBC/OLEDB：不支持 TVP，需用方案一/二。

優勢總結

避坑指南

• 舊版本兼容：SQL 2000 僅能用動態 SQL 或臨時表 `INSERT #t EXEC('SELECT ' + @ids)`；
• 統計信息：TVP 表變量無統計信息，複雜查詢可 `OPTION (RECOMPILE)` 或物化到 `#temp`；
• NULL/空值：分割函數需處理 `@List = ''` 返回空表，避免 `IN (NULL)` 語義陷阱；
• 權限：TVP 需對類型 `EXECUTE` 權限，部署腳本同步授權。

總結

拒絕動態 SQL 不僅是安全合規要求，更是性能穩定性的基石。TVP 是現代首選（類型安全、零解析、計劃友好），XML 分割是通用備胎（無版本依賴、純 T-SQL），CLR 分割是極致性能選項。根據版本、客戶端框架、數據量級選擇最適方案，將「逗號字符串 IN 查詢」納入團隊代碼規範標準庫。