如何在存储过程中循环调用UspCreatePayment处理自定义表类型参数

问题背景 #

现有存储过程UspCreatePayment包含复杂业务逻辑，单条处理性能良好，但在.Net Core中间件中批量处理（如2000条数据）时，因数据库往返次数过多导致性能问题。目前计划通过用户定义表类型参数传入批量数据到新存储过程，由新存储过程循环调用原UspCreatePayment来减少往返次数，需要解决循环中调用原存储过程的具体实现问题。

现有存储过程定义 #

CREATE PROCEDURE UspCreatePayment(InvoiceId, UserId, ProcessorId, TransactionAmount)
BEGIN
-- 业务逻辑
END

用户定义表类型 #

用于传递批量数据的表类型：

CREATE TYPE typeCreatePayment AS TABLE ( 
            [InvoiceId] [NVARCHAR](36),
            [UserId] [UNIQUEIDENTIFIER], 
            [ProcessorId] [NVARCHAR](32), 
            [TransactionAmount] MONEY
        )

待完善的批量存储过程 #

原批量存储过程框架（需补充调用逻辑）：

CREATE PROCEDURE UspCreatePaymentBulk(
                                @typeCreatePayment typeCreatePayment READONLY
        )
AS
  BEGIN
      DECLARE @RowCount BIGINT;
      DECLARE @CurrentRow BIGINT;

      SET @RowCount = (SELECT COUNT(*)
                        FROM @typeCreatePayment )

      WHILE(@CurrentRow <= @RowCount)
        BEGIN
            -- 调用存储过程创建支付记录
            --- 如何在这里调用EXEC UspCreatePayment？
            SET @CurrentRow = @CurrentRow + 1
        END
  END

GO 

具体实现方案 #

以下提供两种可行的循环调用方式，均能实现逐行读取表类型参数并调用原存储过程：

方案1：使用游标遍历（推荐） #

游标是SQL Server中逐行处理数据的标准方式，逻辑清晰且性能稳定：

CREATE PROCEDURE UspCreatePaymentBulk(
    @typeCreatePayment typeCreatePayment READONLY
)
AS
BEGIN
    -- 声明变量存储每行的参数值
    DECLARE @InvoiceId NVARCHAR(36),
            @UserId UNIQUEIDENTIFIER,
            @ProcessorId NVARCHAR(32),
            @TransactionAmount MONEY;

    -- 声明游标，读取表类型中的所有行
    DECLARE paymentCursor CURSOR FOR
        SELECT InvoiceId, UserId, ProcessorId, TransactionAmount
        FROM @typeCreatePayment;

    -- 打开游标
    OPEN paymentCursor;

    -- 读取第一行数据到变量
    FETCH NEXT FROM paymentCursor INTO @InvoiceId, @UserId, @ProcessorId, @TransactionAmount;

    -- 循环遍历所有行
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用原存储过程（使用命名参数避免顺序错误）
        EXEC UspCreatePayment 
            @InvoiceId = @InvoiceId,
            @UserId = @UserId,
            @ProcessorId = @ProcessorId,
            @TransactionAmount = @TransactionAmount;

        -- 读取下一行数据
        FETCH NEXT FROM paymentCursor INTO @InvoiceId, @UserId, @ProcessorId, @TransactionAmount;
    END

    -- 关闭并释放游标
    CLOSE paymentCursor;
    DEALLOCATE paymentCursor;
END
GO

方案2：使用行号循环 #

如果不想使用游标，可以通过给数据添加行号的方式实现循环：

CREATE PROCEDURE UspCreatePaymentBulk(
    @typeCreatePayment typeCreatePayment READONLY
)
AS
BEGIN
    DECLARE @RowCount BIGINT;
    DECLARE @CurrentRow BIGINT = 1; -- 初始化当前行号为1
    DECLARE @InvoiceId NVARCHAR(36),
            @UserId UNIQUEIDENTIFIER,
            @ProcessorId NVARCHAR(32),
            @TransactionAmount MONEY;

    -- 给批量数据添加行号，存储到CTE中
    WITH NumberedPayments AS (
        SELECT 
            InvoiceId, 
            UserId, 
            ProcessorId, 
            TransactionAmount,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum -- 若有业务排序需求可替换排序规则
        FROM @typeCreatePayment
    )
    SELECT @RowCount = COUNT(*) FROM NumberedPayments;

    -- 循环处理每一行
    WHILE @CurrentRow <= @RowCount
    BEGIN
        -- 读取当前行的参数值
        SELECT 
            @InvoiceId = InvoiceId,
            @UserId = UserId,
            @ProcessorId = ProcessorId,
            @TransactionAmount = TransactionAmount
        FROM NumberedPayments
        WHERE RowNum = @CurrentRow;

        -- 调用原存储过程
        EXEC UspCreatePayment 
            @InvoiceId = @InvoiceId,
            @UserId = @UserId,
            @ProcessorId = @ProcessorId,
            @TransactionAmount = @TransactionAmount;

        -- 行号自增
        SET @CurrentRow = @CurrentRow + 1;
    END
END
GO

额外注意事项 #

1. 调用原存储过程时建议使用命名参数，避免因参数顺序变更导致业务错误；
2. 若需保证批量操作的原子性，可在批量存储过程中添加事务逻辑（BEGIN TRANSACTION/COMMIT/ROLLBACK）；
3. 该方案仅为过渡方案，长期来看，直接修改原UspCreatePayment支持批量处理（直接操作表类型参数）能获得更好的性能。

内容的提问来源于stack exchange，提问作者Ravi Kiran