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;
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!).
14 People are following this question.
