CODEWITHSUNDEEP
sql-servert-sql
Sandra
Sandra

Reputation: 129

Infinite While Loop T-SQL

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.

Upvotes: 0

Views: 396

Answers (3)

guaroa mendez
guaroa mendez

Reputation: 11

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

Upvotes: 0

Sandra
Sandra

Reputation: 129

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.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 7

Related Questions