question

sqlLearner 1 avatar image
sqlLearner 1 asked

Loop returning last value twice

I have written this loop but it is returning the last record twice. Any suggestions on how I can avoid this? DECLARE @data VARCHAR(255), @ID VARCHAR(255) DECLARE test_cursor CURSOR FOR SELECT FieldName, ID FROM TestTbl OPEN test_cursor FETCH NEXT FROM test_cursor INTO @data, @ID PRINT @data + '' + @ID WHILE @@FETCH_STATUS -1 BEGIN FETCH NEXT FROM test_cursor INTO @data, @ID PRINT @data + '' + @ID END CLOSE test_cursor; DEALLOCATE test_cursor;
tsqlcursorloop
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave_Green avatar image
Dave_Green answered
Hello, The reason why is because the WHILE only evaluates at the end of the loop - so on the loop with the last value the @data and @ID values are set, and the @@FETCH_STATUS = 0, so we loop again. The FETCH NEXT fails so the variables are left set as they were, and the print re-runs (so we get a second print of the last value). The solution is to make the FETCH NEXT statement the last in the loop, but you will also want to remove the PRINT statement that you have before the WHILE as this would otherwise duplicate the first statement! So, the example becomes: DECLARE @data VARCHAR(255), @ID VARCHAR(255) DECLARE test_cursor CURSOR FOR SELECT FieldName, ID FROM TestTbl OPEN test_cursor FETCH NEXT FROM test_cursor INTO @data, @ID WHILE @@FETCH_STATUS -1 BEGIN PRINT @data + '' + @ID FETCH NEXT FROM test_cursor INTO @data, @ID END CLOSE test_cursor; DEALLOCATE test_cursor; There are many schools of thought as to when and if cursors should be used in databases - I believe they have their place, but you should carefully consider whether these are the best solution for your need (as I'm guessing this is a very simplified example!).
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you!!! It was simply that my print was below the 'FETCH NEXT FROM test_cursor' so I reversed their positions as you show. Solved.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.