SQL Server 傳遞陣列參數的實用替代方案

開發 DNN 模組時常遇到需批次插入多筆資料的需求，但 SQL Server 原生不支援陣列參數。本文分享兩種將字串轉為資料集的高效變通做法，並附上完整預存程式碼。

方案一：字串分割法（相容舊版）

將陣列格式化為 1,2,3,4,5 字串傳入，在預存程序中使用 CHARINDEX 搭配 SUBSTRING 逐一解析：

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductId_Array VARCHAR(800),
    @ModuleId INT
AS
BEGIN
    DECLARE @PointerPrev INT = 1, @PointerCurr INT, @TId INT;
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;

    -- 解析第一個元素
    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 TRANSACTION ELSE ROLLBACK TRANSACTION;
    SET NOCOUNT OFF;
END

方案二：OpenXML 法（SQL 2000+，效能更佳）

網友 Bizlogic 建議改用 sp_xml_preparedocument 將字串轉為 XML 文件，再透過 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, 
        N'' + REPLACE(@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

效能與適用性比較

建議 SQL Server 2000 以上環境優先採用 OpenXML 方案；舊版環境則使用字串分割法並注意字串長度限制（8000 字元）。