Solving A Problem With Cursors
Solution 1:
Either...
FETCH NEXT FROM myCursor INTO@ContactName
WHILE @@FETCH_STATUS =0BEGIN-- do stuffFETCH NEXT FROM myCursor INTO@ContactNameEND
Or...
WHILE @@FETCH_STATUS =0BEGINFETCH NEXT FROM myCursor INTO@ContactName
IF @@FETCH_STATUS =0BEGIN-- do stuffENDEND
Or...
WHILE (1=1)
BEGINFETCH NEXT FROM myCursor INTO@ContactName
IF @@FETCH_STATUS <>0
BREAK
-- do stuffEND
Solution 2:
You mentioned you're using SQL Server 2008. With SQL Server 2005 or greater, you don't need a cursor at all to do what you want.
select top 10left(cast(row_number() over(orderby ContactName) asvarchar)+' ', 6) + ContactName
from Customers
Solution 3:
See how you have the printing logic duplicated? That's a pointer to what's going wrong. Your loop should look like this:
FETCH NEXT INTO@working_variables
WHILE @@FETCH_STATUS =0-- process @working_variablesFETCH NEXT INTO@working_variables
The only duplicated code should be the FETCH NEXT
itself - the way you have it now, the last FETCH
happens, but you PRINT
a line before the WHILE
can exit.
Solution 4:
A FETCH at the end of the record set sets @@FETCH_STATUS to not 0.
The FETCH NEXT command should be the last line in the WHILE BLOCK.
USE Northwind
GO
DECLARE myCursor CURSORFORSELECT TOP(10) ContactName FROM Customers
DECLARE@RowNoint,@ContactName nvarchar(30)
SET@RowNo=0OPEN myCursor
FETCH NEXT FROM myCursor INTO@ContactName
WHILE @@FETCH_STATUS=0BEGINSET@RowNo=@RowNo+1SET@ContactName=''
PRINT +LEFT(CAST(@rowNoasvarchar) +' ',6)+' '+@ContactNameFETCH NEXT FROM myCursor INTO@ContactNameENDCLOSE myCursor
DEALLOCATE myCursor
Solution 5:
This is an off-by-one error. Here's a better way to iterate through a cursor, w/ less code duplication:
USE Northwind
GO
DECLARE myCursor CURSORFORSELECT TOP(10) ContactName FROM Customers
DECLARE@RowNoint,@ContactName nvarchar(30)
SET@RowNo=0-- initialize counters at zero, increment after the fetch/breakOPEN myCursor
WHILE 1=1BEGIN-- start an infinite loopFETCH NEXT FROM myCursor INTO@ContactName
IF @@FETCH_STATUS <>0 BREAK
SET@RowNo=@RowNo+1
PRINT LEFT(CAST(@rowNoasvarchar) +' ',6)+' '+@ContactNameENDCLOSE myCursor
DEALLOCATE myCursor
For extra points, use a cursor variable and declare w/ FAST_FORWARD and TYPE_WARNING, or STATIC for small datasets. eg:
DECLARE@cursorCURSORSET@cursor=CURSOR FAST_FORWARD TYPE_WARNING FORSELECT TOP (10) ContactName FROM Customers
OPEN@cursor
......
CLOSE@cursorDEALLOCATE@cursor
CLOSE and DEALLOCATE are not strictly necessary, as the cursor variable will go out of scope at the end of the batch. It is still good form, however, as you might add more code at the end later on, and you should free up resources as early as possible.
TYPE_WARNING tells you when SQL Server implicitly converts the requested cursor type (FAST_FORWARD) to another type (typically STATIC), if the requested type is incompatible w/ your SELECT statement.
Post a Comment for "Solving A Problem With Cursors"