如何按不同UID分批执行存储过程处理表中对应每行数据

原有代码问题 #

• 声明VARCHAR类型未指定长度，默认长度为1会导致数据截断，逻辑直接失效
• 内层游标仅遍历UID值，未读取当前UID对应所有行的其余字段参数，也没有实际关联行调用存储过程的逻辑，因此无法实现按UID批量处理的要求

方案1：优化游标实现（推荐，代码简洁性能足够） #

使用FAST_FORWARD类型游标（只读、只进，性能开销极低），外层遍历唯一UID，内层遍历当前UID下所有行调用存储过程，完全符合业务要求：

-- 声明存储过程需要的所有入参变量，根据实际字段类型调整
DECLARE 
    @UID VARCHAR(50),
    @Version INT,
    @Site VARCHAR(10),
    @QuestionOI INT,
    @GeneralAnswer VARCHAR(100)

-- 外层游标：遍历所有唯一UID，保证按UID分批
DECLARE uid_cursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT UID FROM #InsertTable
ORDER BY UID -- 可按需要调整排序规则

OPEN uid_cursor
FETCH NEXT FROM uid_cursor INTO @UID

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing UID: ' + @UID
    -- 内层游标：读取当前UID对应的所有行参数，循环调用存储过程
    DECLARE row_cursor CURSOR FAST_FORWARD FOR
    SELECT Version, Site, QuestionOI, GeneralAnswer 
    FROM #InsertTable 
    WHERE UID = @UID

    OPEN row_cursor
    FETCH NEXT FROM row_cursor INTO @Version, @Site, @QuestionOI, @GeneralAnswer

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用存储过程，按实际参数顺序调整
        EXEC stored_proc @UID, @Version, @Site, @QuestionOI, @GeneralAnswer
        FETCH NEXT FROM row_cursor INTO @Version, @Site, @QuestionOI, @GeneralAnswer
    END

    CLOSE row_cursor
    DEALLOCATE row_cursor

    FETCH NEXT FROM uid_cursor INTO @UID
END

CLOSE uid_cursor
DEALLOCATE uid_cursor
PRINT 'All tasks completed'

方案2：无游标循环实现 #

如果不想使用游标，可以用临时表加行号的方式实现，逻辑同样直观：

-- 生成带唯一行号的临时表，区分不同UID和同一UID下的行
SELECT 
    DENSE_RANK() OVER (ORDER BY UID) AS uid_batch, -- 同一UID批次号相同
    ROW_NUMBER() OVER (PARTITION BY UID ORDER BY (SELECT 1)) AS row_in_batch,
    UID, Version, Site, QuestionOI, GeneralAnswer
INTO #temp_process
FROM #InsertTable

DECLARE 
    @current_batch INT = 1,
    @max_batch INT,
    @current_row INT,
    @max_row INT,
    @UID VARCHAR(50),
    @Version INT,
    @Site VARCHAR(10),
    @QuestionOI INT,
    @GeneralAnswer VARCHAR(100)

SELECT @max_batch = MAX(uid_batch) FROM #temp_process

WHILE @current_batch <= @max_batch
BEGIN
    -- 取当前批次UID
    SELECT TOP 1 @UID = UID FROM #temp_process WHERE uid_batch = @current_batch
    PRINT 'Processing UID: ' + @UID

    SELECT @current_row = 1, @max_row = MAX(row_in_batch) 
    FROM #temp_process 
    WHERE uid_batch = @current_batch

    -- 处理当前UID下所有行
    WHILE @current_row <= @max_row
    BEGIN
        SELECT 
            @Version = Version,
            @Site = Site,
            @QuestionOI = QuestionOI,
            @GeneralAnswer = GeneralAnswer
        FROM #temp_process 
        WHERE uid_batch = @current_batch AND row_in_batch = @current_row

        EXEC stored_proc @UID, @Version, @Site, @QuestionOI, @GeneralAnswer
        SET @current_row += 1
    END

    SET @current_batch += 1
END

DROP TABLE #temp_process
PRINT 'All tasks completed'

性能说明 #

5000行数据量级下两种方案的执行效率几乎没有差异，外层循环的开销可以忽略，核心耗时取决于内部存储过程的执行速度。

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