Infinite While Loop T-SQL

Question

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.

What is in your AddStudent procedure? Is it adding rows to the stud table? — Sean Lange
– Sean Lange, Commented Mar 28, 2018 at 14:33
AddStud is adding students from stud table to another table called StudentGroups. — Sandra
– Sandra, Commented Mar 28, 2018 at 14:34
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. — Sean Lange
– Sean Lange, Commented Mar 28, 2018 at 14:35

Zohar Peled · Accepted Answer · 2018-03-28 14:44:56Z

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.

edited Mar 28, 2018 at 14:44

answered Mar 28, 2018 at 14:37

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gottfried Lesigang Over a year ago

You were 25 seconds faster, but I had to type some more characters :-)

Sean Lange Over a year ago

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.

Zohar Peled Over a year ago

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 :-)\

Zohar Peled Over a year ago

@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 :-))

Sean Lange Over a year ago

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. :)

Sandra · Accepted Answer · 2018-03-28 14:54:03Z

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.

answered Mar 28, 2018 at 14:54

Sandra

1291 gold badge2 silver badges8 bronze badges

Comments

guaroa mendez · Accepted Answer · 2018-03-28 15:31:56Z

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.

answered Mar 28, 2018 at 15:31

guaroa mendez

114 bronze badges

Collectives™ on Stack Overflow

Infinite While Loop T-SQL

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related