0 
CREATE OR ALTER PROCEDURE StudentsToGroups AS BEGIN DECLARE @num VARCHAR(15) DECLARE stud_cursor CURSOR FOR SELECT id FROM stud ORDER BY surStud, nameStud OPEN stud_cursor FETCH NEXT FROM stud_cursor INTO @num WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC @status = dbo.AddStudent @num END TRY BEGIN CATCH IF (ERROR_NUMBER() = 2 OR ERROR_NUMBER() = 3) CONTINUE; ELSE IF ERROR_NUMBER() = 4 THROW 5, 'Unsucessful', 1; ELSE THROW; END CATCH FETCH NEXT FROM stud_cursor INTO @num END CLOSE stud_cursor DEALLOCATE stud_cursor END GO EXEC studentsToGroups

Something in my code is causing infinite loop. I really don't get it why because I use FETCH NEXT in While Loop. Also, the procedure is doing it's job, it fills all the students in groups, but it simply never ends.

Improve this question
3
  • 1
    What is in your AddStudent procedure? Is it adding rows to the stud table? Commented Mar 28, 2018 at 14:33
  • AddStud is adding students from stud table to another table called StudentGroups. Commented Mar 28, 2018 at 14:34
  • 1
    Do you really need to use a cursor here? Would be a lot cleaner if you can do this as a set based process instead of loops. Commented Mar 28, 2018 at 14:35

3 Answers 3

Reset to default
7

It's because the CONTINUE.

Any statements after the CONTINUE keyword are ignored.

So if you have any error that returns ERROR_NUMBER() 2 or 3, your FETCH NEXT is ignored and your while loop will run forever.

Please note that using cursors is almost never the correct thing to do in SQL. You better create a new procedure that will add student ids as a batch instead of one by one.

Considering the fact that your AddStudent procedure only takes a single variable, it might be as simple as

INSERT INTO dbo.Student (Id) SELECT id FROM stud -- order by will probably be ignored anyway since database tables are onsorted by nature.
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You were 25 seconds faster, but I had to type some more characters :-)
Had not read about continue before. That sounds a lot like the old VB6 nonsense of "on error resume next", or "if there is an error just ignore it and pretend like nothing happened". Yuck. That is an anti-pattern inside of an anti-pattern. No wonder that cursor mess doesn't work as expected.
That's a lesson I've learned with FGITW answers - start with the minimal answer and edit as you go. That way you have a better chance of being the first :-)\
@SeanLange actually, continue is far less terrible then that nasty VB6 statement. In fact, it might even be useful (I've used it a few times, but mostly in c# code, never in SQL...yet :-))
I have used it occasionally in c# also. But avoid looping like the plague in t-sql so have never considered any of the odd ways of breaking out of a loop or going to the next iteration. :)
0

Thank you, the problem is solved! :) I added 

IF (ERROR_NUMBER() = 50501 OR ERROR_NUMBER() = 50502) BEGIN FETCH NEXT FROM stud_cursor INTO @JMBAG CONTINUE; END

and now everything works fine.

Also, about the solution, this was our homework where we had to make one procedure that is adding one student to one group, and then the other task was to call that first procedure from another procedure to add all the students in groups.

Improve this answer

Comments

0

CONTINUE function breaks the current loop so the cursor fetch (FETCH NEXT FROM stud_cursor INTO @num) will not fire for the next interation.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.