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