如何将拆分后的两列值与@invoiceId一同插入SQL的invoiceItems表

要实现将拆分后的productId、productQuantity与指定的@invoiceId一同插入invoiceItems表，核心是确保两个拆分后的列表按原始顺序一一对应，以下是两种适配不同SQL Server版本的实现方式：

方法一：SQL Server 2022及以上版本（推荐） #

利用STRING_SPLIT新增的ordinal参数（第三个参数设为1），直接获取元素在原字符串中的位置，以此关联两个拆分结果：

-- 存储过程中需先定义@invoiceId，示例假设为INT类型，根据实际调整
DECLARE @invoiceId INT = 123;
DECLARE @productId NVARCHAR(MAX) = '1,2,3,4';
DECLARE @productQuantity NVARCHAR(MAX) = '1.1,2.2,3.3,4.4';

INSERT INTO invoiceItems (invoiceId, productId, productQuantity)
SELECT 
    @invoiceId,
    CAST(pid.value AS INT) AS productId,
    CAST(pqty.value AS DECIMAL(10,1)) AS productQuantity
FROM STRING_SPLIT(@productId, ',', 1) AS pid
JOIN STRING_SPLIT(@productQuantity, ',', 1) AS pqty
    ON pid.ordinal = pqty.ordinal;

说明 #

STRING_SPLIT的ordinal列会返回元素在原逗号分隔字符串中的位置序号，通过该序号关联两个拆分结果，能保证每个productId匹配对应的productQuantity。

方法二：SQL Server 2019及更早版本 #

若不支持STRING_SPLIT的ordinal参数，可通过XML拆分结合ROW_NUMBER()生成序号来实现关联：

DECLARE @invoiceId INT = 123;
DECLARE @productId NVARCHAR(MAX) = '1,2,3,4';
DECLARE @productQuantity NVARCHAR(MAX) = '1.1,2.2,3.3,4.4';

-- 拆分productId并生成序号
WITH SplitProductIds AS (
    SELECT 
        CAST(Split.value AS INT) AS productId,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (SELECT CAST('<v>' + REPLACE(@productId, ',', '</v><v>') + '</v>' AS XML) AS XmlData) AS x
    CROSS APPLY XmlData.nodes('/v') AS T(Split)
),
-- 拆分productQuantity并生成序号
SplitProductQuantities AS (
    SELECT 
        CAST(Split.value AS DECIMAL(10,1)) AS productQuantity,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (SELECT CAST('<v>' + REPLACE(@productQuantity, ',', '</v><v>') + '</v>' AS XML) AS XmlData) AS x
    CROSS APPLY XmlData.nodes('/v') AS T(Split)
)

INSERT INTO invoiceItems (invoiceId, productId, productQuantity)
SELECT 
    @invoiceId,
    spi.productId,
    spq.productQuantity
FROM SplitProductIds spi
JOIN SplitProductQuantities spq ON spi.ordinal = spq.ordinal;

注意事项 #

• 必须保证@productId和@productQuantity的元素数量完全一致，否则会出现数据不匹配或丢失。建议在存储过程中添加校验逻辑，比如比较两个拆分后的行数是否相等。
• 根据invoiceItems表中invoiceId的实际数据类型，调整@invoiceId的声明类型（例如UNIQUEIDENTIFIER）。

内容的提问来源于stack exchange，提问作者Ramy Salem