萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 傳遞陣列參數的兩種變通方案:字符串分割與 OpenXML

背景

SQL Server 無原生陣列參數支援。需一次性插入 N 筆資料 (如產品 ID 列表) 時,常見變通:將陣列序列化為字串 "1,2,3,4,5,6" 傳入存儲過程,再在 T-SQL 中分割處理。

方案一:字串分割 (Substring + CharIndex)

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductId_Array VARCHAR(800),
    @ModuleId INT
AS
DECLARE @PointerPrev INT, @PointerCurr INT, @TId INT;
SET @PointerPrev = 1; SET @PointerCurr = 1;
BEGIN TRANSACTION; SET NOCOUNT ON;
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);

SET NOCOUNT OFF;
IF @@ERROR = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION;
GO

方案二:OpenXML (推薦,SQL 2000+)

網友 Bizlogic 改進:傳入 XML 字串,利用 sp_xml_preparedocument / OPENXML 解析,代碼簡潔、效能更高、可讀性強。

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductId_Array NVARCHAR(2000),
    @ModuleId INT
AS
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;
GO

調用時傳入 XML:<Products><Product ProductId="1"/><Product ProductId="2"/></Products>。

比較總結

維度字串分割OpenXML
代碼複雜度高 (迴圈、指標運算)低 (集合式操作)
效能較差 (逐行插入、多次掃描)優 (批量插入、優化器支援)
可維護性差 (邊界條件易錯)好 (標準 XML 處理)
適用版本所有版本SQL 2000 SP3+ / 2005+

建議:SQL 2000 SP3 以上環境優先採用 OpenXML;更高版本 (2008+) 可直接使用 Table-Valued Parameters (TVP) 獲得原生陣列支援。

copyright © 萬盛學電腦網 all rights reserved