Skip to content Skip to sidebar Skip to footer

Solving A Problem With Cursors

I have a question. I am working on cursors. Each time, after fetching the last records and printing its data’s, the cursor prints an addition line. To understand what I mean plea

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"