CODEWITHSUNDEEP
t-sqlcursor
NealR
NealR

Reputation: 10689

Infinite loop in T-SQL cursor

Below is the code for a T-SQL cursor. It works fine through the first iteration, however after that gets stuck in an infinite loop going between the FETCH NEXT statement and the IF NOT EXISTS statement (basically it will insert the first record, however after that the cursor will not move onto the next record so the IF NOT EXISTS is perpetually false). This is my first time using a cursor so was hoping someone could explain what is going on/how to make this thing work!

    DECLARE prod_cursor CURSOR FOR
    SELECT ProductCode 
    FROM CourseToProduct 
    WHERE CourseCode = @courseCode and (TerminationDate >= @expDate OR TerminationDate IS NULL)

    OPEN prod_cursor

    FETCH NEXT FROM prod_cursor
    INTO @productCode

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF NOT EXISTS
        (
        SELECT sNumber
        FROM AgentProductTraining
        WHERE @sNumber = sNumber and 
              @courseCode = CourseCode and 
              @productCode = ProductCode and 
              @dateTaken = DateTaken
        )
        BEGIN
            IF @sNumber IS NOT NULL
            BEGIN
                INSERT INTO AgentProductTraining
                            (
                             sNumber,
                             CourseCode,
                             ProductCode,
                             DateTaken,
                             DateExpired,
                             LastChangeOperator,
                             LastChangeDate
                            ) 
                VALUES      (
                             @sNumber,
                             @courseCode,
                             @productCode,
                             @dateTaken,
                             COALESCE(@expDate, 'NULL'),
                             @lastChangeOperator,
                             @lastChangeDate
                           )    
            END
        END
    END
    CLOSE prod_cursor;
    DEALLOCATE prod_cursor;

Upvotes: 2

Views: 9579

Answers (1)

James L.
James L.

Reputation: 9451

You have to fetch the next row in the while..end or it will never move to the next record. Like this:

DECLARE prod_cursor CURSOR FOR
SELECT ProductCode 
FROM CourseToProduct 
WHERE CourseCode = @courseCode and (TerminationDate >= @expDate OR TerminationDate IS NULL)

OPEN prod_cursor

FETCH NEXT FROM prod_cursor
INTO @productCode

WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS
    (
    SELECT SymetraNumber
    FROM AgentProductTraining
    WHERE @symetraNumber = SymetraNumber and 
          @courseCode = CourseCode and 
          @productCode = ProductCode and 
          @dateTaken = DateTaken
    )
    BEGIN
        IF @symetraNumber IS NOT NULL
        BEGIN
            INSERT INTO AgentProductTraining
                        (
                         sNumber,
                         CourseCode,
                         ProductCode,
                         DateTaken,
                         DateExpired,
                         LastChangeOperator,
                         LastChangeDate
                        ) 
            VALUES      (
                         @sNumber,
                         @courseCode,
                         @productCode,
                         @dateTaken,
                         COALESCE(@expDate, 'NULL'),
                         @lastChangeOperator,
                         @lastChangeDate
                       )    
        END
    END

    FETCH NEXT FROM prod_cursor
    INTO @productCode
END
CLOSE prod_cursor;
DEALLOCATE prod_cursor;

Upvotes: 9

Related Questions