SQL游标陷入无限循环问题排查求助

你的游标无限循环的核心原因非常典型：你正在从form_question_answer表读取数据，同时在循环体内不断向同一张表插入新记录——默认的游标是动态的，它会持续读取表中新增的行，导致@@FETCH_STATUS永远为0，循环永远无法终止。

具体问题拆解 #

• 你的游标cursor_form_answer查询的是form_question_answer表的现有数据
• 循环体里的INSERT语句不断往这张表添加新行
• 默认的游标类型（动态游标）会实时感知表的变化，新插入的行会被游标继续读取，形成无限循环

另外，你的代码还有两个小问题需要注意：

• 变量@question_answer_id只在注释里声明了，实际没有初始化，插入时会使用NULL值，可能不符合你的业务需求
• FETCH语句里的注释（--@form_question_id, @question_answer_id;）虽然语法上没问题，但容易混淆逻辑，建议清理掉

解决方案 #

方案1：使用静态游标 #

静态游标会创建查询结果的快照，后续对表的修改不会影响游标内的数据，这样就能避免读取到新插入的行。修改游标声明即可：

DECLARE @counter INT = 1042, 
        @overwrite_text NVARCHAR(250), 
        @sequence INT, 
        @is_correct BIT, 
        @score INT, 
        @form_question_id INT, 
        @question_answer_id INT; -- 这里把注释去掉，初始化需要的变量

-- 添加STATIC关键字，创建静态游标
DECLARE cursor_form_answer CURSOR STATIC FOR
SELECT [overwrite_text] ,[sequence] ,[is_correct] ,[score]
FROM [form_question_answer];

OPEN cursor_form_answer;
FETCH NEXT FROM cursor_form_answer INTO @overwrite_text, @sequence, @is_correct, @score;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 注意：这里@question_answer_id需要你提前赋值，比如从原数据读取或者设置默认值
    INSERT INTO [form_question_answer] (overwrite_text, sequence, is_correct, score, form_question_id, question_answer_id)
    VALUES (@overwrite_text, @sequence, @is_correct, @score, @counter, @question_answer_id);
    
    SET @counter = @counter + 1;
    FETCH NEXT FROM cursor_form_answer INTO @overwrite_text, @sequence, @is_correct, @score;
END;

CLOSE cursor_form_answer;
DEALLOCATE cursor_form_answer;

方案2：先把数据存入临时表/表变量 #

如果不想修改游标类型，可以先将需要复制的数据存入临时表，再从临时表读取数据插入目标表，这样原表的新插入行不会干扰数据读取：

DECLARE @counter INT = 1042, 
        @overwrite_text NVARCHAR(250), 
        @sequence INT, 
        @is_correct BIT, 
        @score INT, 
        @form_question_id INT, 
        @question_answer_id INT;

-- 创建临时表存储原始数据
CREATE TABLE #TempFormAnswers (
    overwrite_text NVARCHAR(250),
    sequence INT,
    is_correct BIT,
    score INT
);

INSERT INTO #TempFormAnswers
SELECT [overwrite_text] ,[sequence] ,[is_correct] ,[score]
FROM [form_question_answer];

-- 基于临时表创建游标
DECLARE cursor_form_answer CURSOR FOR
SELECT overwrite_text, sequence, is_correct, score
FROM #TempFormAnswers;

OPEN cursor_form_answer;
FETCH NEXT FROM cursor_form_answer INTO @overwrite_text, @sequence, @is_correct, @score;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [form_question_answer] (overwrite_text, sequence, is_correct, score, form_question_id, question_answer_id)
    VALUES (@overwrite_text, @sequence, @is_correct, @score, @counter, @question_answer_id);
    
    SET @counter = @counter + 1;
    FETCH NEXT FROM cursor_form_answer INTO @overwrite_text, @sequence, @is_correct, @score;
END;

CLOSE cursor_form_answer;
DEALLOCATE cursor_form_answer;
DROP TABLE #TempFormAnswers; -- 清理临时表

额外建议 #

其实这种批量复制数据的场景，完全可以不用游标，用一条INSERT语句就能实现，效率比游标高得多：

DECLARE @counter INT = 1042;

INSERT INTO [form_question_answer] (overwrite_text, sequence, is_correct, score, form_question_id, question_answer_id)
SELECT 
    overwrite_text, 
    sequence, 
    is_correct, 
    score, 
    @counter + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), -- 生成递增的form_question_id
    @question_answer_id -- 这里需要赋值正确的值
FROM [form_question_answer];

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