SQL Server 傳遞陣列參數的三種變通方案：字串分割、XML 與 Table-Valued Parameters

問題背景

SQL Server（2008 以前版本）不支援陣列或集合類型參數。當應用層需一次傳入多個 ID（如批量刪除、批量插入）時，常見做法是將陣列序列化為逗號分隔字串（'1,2,3,4,5'），再在預存程序中解析處理。

方案一：傳統字串分割法（CHARINDEX + SUBSTRING 迴圈）

早期常見寫法，邏輯直觀但效能較差、程式碼冗長、易出錯：

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductId_Array VARCHAR(800),
    @ModuleId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;
    DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;

    DECLARE @PointerPrev INT = 1, @PointerCurr INT, @TId INT;
    SET @PointerCurr = CHARINDEX(',', @ProductId_Array, @PointerPrev + 1);
    SET @TId = CAST(SUBSTRING(@ProductId_Array, @PointerPrev, @PointerCurr - @PointerPrev) AS INT);
    INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @TId);
    SET @PointerPrev = @PointerCurr;

    WHILE (@PointerPrev + 1 < LEN(@ProductId_Array))
    BEGIN
        SET @PointerCurr = CHARINDEX(',', @ProductId_Array, @PointerPrev + 1);
        IF (@PointerCurr > 0)
        BEGIN
            SET @TId = CAST(SUBSTRING(@ProductId_Array, @PointerPrev + 1, @PointerCurr - @PointerPrev - 1) AS INT);
            INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @TId);
            SET @PointerPrev = @PointerCurr;
        END
        ELSE BREAK;
    END
    -- 處理最後一個元素
    SET @TId = CAST(SUBSTRING(@ProductId_Array, @PointerPrev + 1, LEN(@ProductId_Array) - @PointerPrev) AS INT);
    INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @TId);

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

缺點：純標量迴圈、大量字串操作、效能隨元素數量線性惡化、難以維護。

方案二：OpenXML 解析 XML 字串（SQL 2000/2005 推薦）

將陣列序列化為 XML（<Products><Product>1</Product><Product>2</Product></Products>），利用 sp_xml_preparedocument + OPENXML 集合化查詢：

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductId_Array NVARCHAR(2000),
    @ModuleId INT
AS
BEGIN
    DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;
    IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0) RETURN;

    DECLARE @idoc INT;
    EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array;

    INSERT INTO ProductListSpecial (ModuleId, ProductId)
    SELECT @ModuleId, C.ProductId
    FROM OPENXML(@idoc, '/Products/Product', 3)
    WITH (ProductId INT) AS C
    WHERE C.ProductId IS NOT NULL;

    EXEC sp_xml_removedocument @idoc;
END

優點：集合式操作、效能遠優於迴圈、程式碼簡潔、可讀性強。

注意：需確保呼叫 sp_xml_removedocument 釋放記憶體，避免洩漏。

方案三：Table-Valued Parameters（TVP，SQL Server 2008+ 原生支援）

現代最佳實踐：定義使用者定義資料表類型，直接傳遞資料表變數，無需序列化/反序列化。

-- 1. 建立類型（一次即可）
CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY);

-- 2. 預存程序使用 TVP
CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductIds dbo.IdList READONLY,
    @ModuleId INT
AS
BEGIN
    DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;
    INSERT INTO ProductListSpecial (ModuleId, ProductId)
    SELECT @ModuleId, Id FROM @ProductIds;
END

優勢：型別安全、查詢最佳化器可直接使用統計資訊、支援大量資料、程式碼最簡潔、效能最佳。

方案對比總結

建議

• 新專案若基於 SQL Server 2008+，**強烈建議直接使用 TVP**。
• 維護舊系統（SQL 2000/2005）時，OpenXML 是比字串分割更專業的選擇。
• 避免在高併發、大資料量場景使用字串分割迴圈，極易成為效能瓶頸。