SQL Server 傳遞陣列參數的兩種變通方案：字串分割與 XML 剖析

問題背景

T-SQL 無原生陣列型別，需將多值參數序列化為字串或 XML 傳入預存程序。

方案一：逗號分隔字串 + CHARINDEX/SUBSTRING 迴圈

CREATE PROCEDURE UpdateSpecialList
    @ProductIdArray VARCHAR(800),
    @ModuleId INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @PtrPrev INT = 1, @PtrCurr INT, @PId INT;

    BEGIN TRAN;
    DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;

    -- 處理第一個值 (無前置逗號)
    SET @PtrCurr = CHARINDEX(',', @ProductIdArray, @PtrPrev);
    IF @PtrCurr = 0 SET @PtrCurr = LEN(@ProductIdArray) + 1;
    SET @PId = CAST(SUBSTRING(@ProductIdArray, @PtrPrev, @PtrCurr - @PtrPrev) AS INT);
    INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @PId);
    SET @PtrPrev = @PtrCurr + 1;

    -- 迴圈處理剩餘
    WHILE @PtrPrev <= LEN(@ProductIdArray)
    BEGIN
        SET @PtrCurr = CHARINDEX(',', @ProductIdArray, @PtrPrev);
        IF @PtrCurr = 0 SET @PtrCurr = LEN(@ProductIdArray) + 1;
        SET @PId = CAST(SUBSTRING(@ProductIdArray, @PtrPrev, @PtrCurr - @PtrPrev) AS INT);
        INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @PId);
        SET @PtrPrev = @PtrCurr + 1;
    END

    IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN;
END
GO

方案二：XML 參數 + OPENXML (SQL Server 2000+)

CREATE PROCEDURE UpdateSpecialList_XML
    @ProductIdXML NVARCHAR(2000),
    @ModuleId INT
AS
BEGIN
    SET NOCOUNT ON;
    IF @ProductIdXML IS NULL OR LEN(LTRIM(RTRIM(@ProductIdXML))) = 0 RETURN;

    DECLARE @hDoc INT;
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @ProductIdXML;

    DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;

    INSERT INTO ProductListSpecial (ModuleId, ProductId)
    SELECT @ModuleId, Id
    FROM OPENXML(@hDoc, '/Products/Id', 2)
    WITH (Id INT '.');

    EXEC sp_xml_removedocument @hDoc;
END
GO

前端呼叫範例

// 字串版
string ids = "1,2,3,4,5";
cmd.Parameters.AddWithValue("@ProductIdArray", ids);

// XML 版
string xml = "123";
cmd.Parameters.AddWithValue("@ProductIdXML", xml);

比較與建議

建議：SQL Server 2000/2005 專案優先採用 XML 方案；2008+ 可直接使用 資料表值參數 (TVP)，原生支援陣列傳遞，效能最佳且程式碼最簡潔。