SQL Server 傳遞數組參數的三大方案：字符串分割、XML/JSON、表值參數 (TVP) 全解析

背景：T-SQL 無原生數組，業務頻繁需「批量 ID 查詢/刪除/更新」

典型場景：前端勾選多行 → 傳遞 ID 列表 → 後台 `WHERE Id IN (...)`。若拼接字符串構造 SQL，面臨注入、計劃緩存污染、長度限制 (8000/MAX) 三重風險。

方案對比總覽

方案一：字符串分割 (兼容 2000/2005，僅作存量維護)

1.1 高性能分割函數 (Numbers 表 / Tally Table)

CREATE FUNCTION dbo.SplitString (@List NVARCHAR(MAX), @Delim NCHAR(1))
RETURNS @T TABLE (Item NVARCHAR(4000))
AS
BEGIN
    DECLARE @Pos INT = 1, @Next INT;
    WHILE @Pos <= LEN(@List)
    BEGIN
        SET @Next = CHARINDEX(@Delim, @List, @Pos);
        IF @Next = 0 SET @Next = LEN(@List) + 1;
        INSERT @T VALUES (SUBSTRING(@List, @Pos, @Next - @Pos));
        SET @Pos = @Next + 1;
    END
    RETURN;
END;
GO

-- 調用
SELECT * FROM dbo.SplitString(N'1,2,3,4,5', N',');

1.2 存儲過程調用示例

CREATE PROC dbo.usp_DeleteUsers @IdList NVARCHAR(MAX)
AS
DELETE u FROM dbo.Users u
JOIN dbo.SplitString(@IdList, N',') s ON u.Id = s.Item;
GO

方案二：XML 參數 (2005+，適合複雜結構)

-- 客戶端構造 XML
DECLARE @Xml XML = N'123';

-- 存儲過程解析
CREATE PROC dbo.usp_ProcessXml @Data XML
AS
DELETE u FROM dbo.Users u
JOIN @Data.nodes('/Ids/Id') x(i) ON u.Id = x.i.value('.', 'INT');
GO

優勢：支持巢狀結構、屬性、XSD 驗證；劣勢：XML 解析 CPU 高、序列化體積大、客戶端構造繁瑣。

方案三：JSON 參數 (2016+，Web/API 友好)

-- 客戶端傳遞 JSON 字符串
DECLARE @Json NVARCHAR(MAX) = N'[1,2,3,4,5]';

-- 存儲過程解析 (OPENJSON 需兼容性級別 130+)
CREATE PROC dbo.usp_ProcessJson @Json NVARCHAR(MAX)
AS
DELETE u FROM dbo.Users u
JOIN OPENJSON(@Json) j ON u.Id = j.value;
GO

優勢：原生函數、輕量、JavaScript 直傳、無需預定義類型；劣勢：2016 以下不可用、大數組解析較 XML 慢、無強 Schema。

方案四：表值參數 TVP (2008+，企業級金標準)

4.1 定義用戶表類型

CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY);  -- 可擴展多列
GO

4.2 存儲過程接收 TVP (必須 READONLY)

CREATE PROC dbo.usp_BatchDeleteUsers @Ids dbo.IdList READONLY
AS
DELETE u FROM dbo.Users u
JOIN @Ids i ON u.Id = i.Id;
GO

4.3 .NET 客戶端調用 (DataTable / SqlDataRecord / IEnumerable)

// C# 示例：DataTable 方式
var dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
foreach (var id in selectedIds) dt.Rows.Add(id);

using (var cmd = new SqlCommand("usp_BatchDeleteUsers", conn) { CommandType = CommandType.StoredProcedure })
{
    var p = cmd.Parameters.AddWithValue("@Ids", dt);
    p.SqlDbType = SqlDbType.Structured;
    p.TypeName = "dbo.IdList";
    cmd.ExecuteNonQuery();
}

4.4 TVP 核心優勢深度解析

• 統計信息：TVP 內部為表變量，編譯時生成統計，優化器可估算行數，選擇最優連接算子 (Nested Loops / Hash / Merge)。
• 計劃復用：同一 TVP 類型、相似行數分布，計劃緩存命中率極高，避免 `IN` 列表長度變化導致的計劃爆炸。
• 並行安全：`READONLY` 保證無副作用，支持並行計劃。
• 類型安全：編譯期檢查列名/類型/約束，杜絕注入與隱式轉換。
• 批量 DML：`MERGE` / `INSERT...SELECT` / `UPDATE...JOIN` 原生支持，性能遠超循環調用。

選型決策樹

1. SQL Server ≥ 2008 且 .NET/Java 客戶端可控？→ **TVP (首選)**。
2. 跨語言/異構微服務/REST API？→ **JSON (2016+)** / **XML (2005+)**。
3. 存量 2000/2005 系統、無法變更客戶端？→ **字符串分割 + 緩存分割函數結果**。
4. 極簡臨時腳本、DBA 手工執行？→ **字符串分割 / XML 字面量**。

避坑指南