SQL Server 預存程序傳遞陣列參數三大替代方案實戰

前言

SQL Server 無陣列型別，ANSI SQL-92 亦未定義。但實務上常需傳遞多值給 IN 子句或批次處理。本文提供三種成熟替代方案。

方案一：逗號分隔字串 + WHILE 迴圈解析

CREATE PROC usp_ParseCSV @csv VARCHAR(8000)
AS
DECLARE @pos INT, @val VARCHAR(100)
SET @csv = @csv + ','  -- 統一結尾
WHILE LEN(@csv) > 0
BEGIN
    SET @pos = CHARINDEX(',', @csv)
    SET @val = LTRIM(RTRIM(SUBSTRING(@csv, 1, @pos - 1)))
    IF @val <> '' 
        PRINT @val  -- 此處插入暫存表或執行邏輯
    SET @csv = SUBSTRING(@csv, @pos + 1, LEN(@csv))
END

優點：單一參數、呼叫簡單。缺點：解析效能差、易受注入攻擊 (需嚴格驗證)、長度限制 8000 字元。

方案二：暫存表 / 資料表值參數 (TVP) - 推薦

SQL Server 2000/2005：暫存表模式

-- 呼叫端建立暫存表
CREATE TABLE #MyList (Item VARCHAR(50))
INSERT #MyList VALUES ('A'), ('B'), ('C')

-- 預存程序接收暫存表名稱
CREATE PROC usp_UseTempTable @TempTableName VARCHAR(100)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * FROM ' + @TempTableName
EXEC sp_executesql @sql
-- 或 INSERT 到內部暫存表再 JOIN

SQL Server 2008+：資料表值參數 (TVP) - 最佳實務

-- 1. 建立使用者定義資料表型別
CREATE TYPE dbo.StringList AS TABLE (Item VARCHAR(50) PRIMARY KEY);

-- 2. 預存程序使用 TVP
CREATE PROC usp_UseTVP @List dbo.StringList READONLY
AS
SELECT t.* FROM TargetTable t
INNER JOIN @List l ON t.KeyCol = l.Item;

-- 3. 呼叫端 (ADO.NET 範例)
DataTable dt = new DataTable();
dt.Columns.Add("Item", typeof(string));
dt.Rows.Add("A"); dt.Rows.Add("B"); dt.Rows.Add("C");

using (SqlCommand cmd = new SqlCommand("usp_UseTVP", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter p = cmd.Parameters.AddWithValue("@List", dt);
    p.SqlDbType = SqlDbType.Structured;
    p.TypeName = "dbo.StringList";
    cmd.ExecuteNonQuery();
}

TVP 優勢：強型別、效能極佳 (內存傳輸)、無 SQL 注入風險、可直接 JOIN、支援統計資料。

方案三：動態 SQL 構建 IN 子句 (簡單場景適用)

CREATE PROC usp_DynamicIN @CSV VARCHAR(8000)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * FROM MyTable WHERE KeyCol IN (' + @CSV + ')'
EXEC sp_executesql @sql
-- 呼叫：EXEC usp_DynamicIN '''A'',''B'',''C'''

極高風險：SQL 注入溫床。**僅限受信任內部呼叫、嚴格驗證輸入格式**。生產環境**強烈不建議**。

方案比較總結

最佳實務建議

1. SQL Server 2008 以上：**優先使用 TVP**
2. 舊版本：暫存表 + 內部複製到區域暫存表 (#internal) 再 JOIN
3. 絕不在動態 SQL 直接拼接使用者輸入
4. 大量值 (超過 1000) 考慮分批或使用 BULK INSERT / SqlBulkCopy