SQL Server 傳遞數組參數的三種變通方案：字符串分割、XML、表值參數

SQL Server 無原生數組類型，批量傳遞 ID 列表需變通。三代方案演進：

方案一：字符串分割（SQL 2000 通用）

CREATE PROCEDURE dbo.ProductListUpdate
  @IdList VARCHAR(8000),  -- '1,2,3,4,5'
  @ModuleId INT
AS
DECLARE @Pos INT = 1, @NextPos INT, @Id INT
DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId
WHILE @Pos <= LEN(@IdList)
BEGIN
  SET @NextPos = CHARINDEX(',', @IdList, @Pos)
  IF @NextPos = 0 SET @NextPos = LEN(@IdList) + 1
  SET @Id = CAST(SUBSTRING(@IdList, @Pos, @NextPos - @Pos) AS INT)
  INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @Id)
  SET @Pos = @NextPos + 1
END

缺點：字符串操作慢、易注入、維護難。

方案二：XML 解析（SQL 2000+ 推薦）

CREATE PROCEDURE dbo.ProductListUpdate
  @IdXml NVARCHAR(MAX),  -- ''
  @ModuleId INT
AS
DECLARE @Doc INT
EXEC sp_xml_preparedocument @Doc OUTPUT, @IdXml
DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId
INSERT INTO ProductListSpecial (ModuleId, ProductId)
SELECT @ModuleId, Id
FROM OPENXML(@Doc, '/Products/Product', 2) WITH (Id INT)
EXEC sp_xml_removedocument @Doc

優點：結構化、可擴展屬性、性能優於字符串分割。

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

-- 1. 創建類型
CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY)

-- 2. 存儲過程
CREATE PROCEDURE dbo.ProductListUpdate
  @Ids dbo.IdList READONLY,
  @ModuleId INT
AS
DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId
INSERT INTO ProductListSpecial (ModuleId, ProductId)
SELECT @ModuleId, Id FROM @Ids

-- C# 調用
var dt = new DataTable(); dt.Columns.Add("Id", typeof(int));
foreach (var id in ids) dt.Rows.Add(id);
cmd.Parameters.AddWithValue("@Ids", dt);

優點：強類型、集合化、計劃緩存友好、統計信息準確、無注入風險。

選型建議

• SQL 2000/2005 必須用 → XML 方案
• SQL 2008+ 新開發 → **強制 TVP**
• 避免字符串分割進入生產環境