CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
sicKo
sicKo

Reputation: 1256

SQL cursor goes into infinite loop

This sql goes into infinite loop.

I can't find what wrong with it. I have searched other post but most of it, they missed the last fetch statement before the end tag. I'm sure I mislooked something but I can't tell what it is. So did I missed anything?

Below are my codes

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

DECLARE cursor_form_answer CURSOR
FOR SELECT
      [overwrite_text]
      ,[sequence]
      ,[is_correct]
      ,[score]
      --,[form_question_id]
      ,[question_answer_id]
  FROM [form_question_answer];

OPEN cursor_form_answer;

FETCH NEXT FROM cursor_form_answer INTO 
    @overwrite_text, 
    @sequence,
    @is_correct,
    @score,
    --@form_question_id,
    @question_answer_id;

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,
            --@form_question_id,
            @question_answer_id;
    END;

CLOSE cursor_form_answer;

DEALLOCATE cursor_form_answer;

Upvotes: 0

Views: 795

Answers (3)

lptr
lptr

Reputation: 6788

By default the cursor is dynamic (it reads the table on each iteration). In your code, every time you are iterating you are also inserting new rows and the cursor never reaches "the end" of the table.

If you want a single pass on the existing data, just declare the cursor as static:

DECLARE cursor_form_answer CURSOR STATIC

or you can limit the scope of the dynamic cursor to existing rows only, if your data support it, eg. there is an identity column (there is a nuance/"fine" line here regarding the cursor evaluation)

--this.. if sequence is autoincrement
DECLARE cursor_form_answer CURSOR 
FOR SELECT
      ...
from [form_question_answer]
where [sequence] <= (select max([sequence]) from [form_question_answer])

Upvotes: 1

Dona
Dona

Reputation: 78

Yes, it does because you inserted records in the same table that you use to loop. For example if you have have a record in the table, the record you insert will be the next fetch, so will be an infinite loop.

Upvotes: 1

Thom A
Thom A

Reputation: 95554

There's no need for a CURSOR here at all. Just INSERT the data using a SELECT:

INSERT INTO [form_question_answer] (overwrite_text,
                                    [sequence],
                                    is_correct,
                                    score,
                                    form_question_id,
                                    question_answer_id)
SELECT [overwrite_text],
       [sequence],
       [is_correct],
       [score],
       --,[form_question_id]
       ROW_NUMBER() OVER (ORDER BY [form_question_id]),
       [question_answer_id]
FROM [form_question_answer];

I must say, however, it seems really odd that you're capturing data from the table form_question_answer and then inserting it back into said table.

Upvotes: 0

Related Questions