Solving the problem with cursors

Question

I have a question. I work on cursors. Each time, after retrieving the latest records and printing their data, the cursor prints an addition line. To understand what I mean, consider the following example: I want to print information about only 10 clients.

USE Northwind GO DECLARE myCursor CURSOR FOR SELECT TOP(10) ContactName FROM Customers DECLARE @RowNo int,@ContactName nvarchar(30) SET @RowNo=1 OPEN myCursor FETCH NEXT FROM myCursor INTO @ContactName PRINT LEFT(CAST(@rowNo as varchar) + ' ',6)+' '+ @ContactName SET @ RowNo=@RowNo +1 SET @ContactName='' WHILE @@FETCH_STATUS=0 BEGIN FETCH NEXT FROM myCursor INTO @ContactName PRINT + LEFT(CAST(@rowNo as varchar) + ' ',6)+' '+ @ContactName SET @ RowNo=@RowNo +1 SET @ContactName='' END CLOSE myCursor DEALLOCATE myCursor 

Now let's look at the result:

 1 Maria Anders 2 Ana Trujillo 3 Antonio Moreno 4 Thomas Hardy 5 Christina Berglund 6 Hanna Moos 7 Frédérique Citeaux 8 Martín Sommer 9 Laurence Lebihan 10 Elizabeth Lincoln 11 

Line number 11 was also printed. Is this a problem in the cursor, or does it always occur? Is there a way to not print the addition data? thanks (I am using sql erver 2008)

Answer 1

Or...

 FETCH NEXT FROM myCursor INTO @ContactName WHILE @@FETCH_STATUS = 0 BEGIN -- do stuff FETCH NEXT FROM myCursor INTO @ContactName END 

Or...

 WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM myCursor INTO @ContactName IF @@FETCH_STATUS = 0 BEGIN -- do stuff END END 

Or...

 WHILE (1 = 1) BEGIN FETCH NEXT FROM myCursor INTO @ContactName IF @@FETCH_STATUS <> 0 BREAK -- do stuff END 

Answer 2

You mentioned that you are using SQL Server 2008. With SQL Server 2005 and above, you do not need a cursor at all to do what you want.

 select top 10 left(cast(row_number() over(order by ContactName) as varchar)+ ' ', 6) + ContactName from Customers 

Answer 3

See how print logic is duplicated? This is a pointer to what is going wrong. Your loop should look like this:

 FETCH NEXT INTO @working_variables WHILE @@FETCH_STATUS = 0 -- process @working_variables FETCH NEXT INTO @working_variables 

The only duplicated code should be FETCH NEXT - the way you have it, the last FETCH happens, but you PRINT can exit the line before WHILE .

Answer 4

A FETCH at the end of the record set @@ FETCH_STATUS is not 0.

The FETCH NEXT command must be the last line in WHILE BLOCK.

 USE Northwind GO DECLARE myCursor CURSOR FOR SELECT TOP(10) ContactName FROM Customers DECLARE @RowNo int,@ContactName nvarchar(30) SET @RowNo=0 OPEN myCursor FETCH NEXT FROM myCursor INTO @ContactName WHILE @@FETCH_STATUS=0 BEGIN SET @ RowNo=@RowNo +1 SET @ContactName='' PRINT + LEFT(CAST(@rowNo as varchar) + ' ',6)+' '+ @ContactName FETCH NEXT FROM myCursor INTO @ContactName END CLOSE myCursor DEALLOCATE myCursor 

Answer 5

This is a mistake at a time. Here's the best way to iterate over a cursor without duplicating code:

 USE Northwind GO DECLARE myCursor CURSOR FOR SELECT TOP(10) ContactName FROM Customers DECLARE @RowNo int,@ContactName nvarchar(30) SET @RowNo=0 -- initialize counters at zero, increment after the fetch/break OPEN myCursor WHILE 1=1 BEGIN -- start an infinite loop FETCH NEXT FROM myCursor INTO @ContactName IF @@FETCH_STATUS <> 0 BREAK SET @ RowNo=@RowNo +1 PRINT LEFT(CAST(@rowNo as varchar) + ' ',6)+' '+ @ContactName END CLOSE myCursor DEALLOCATE myCursor 

For extra points, use the cursor variable and declare w / FAST_FORWARD and TYPE_WARNING or STATIC for small datasets. eg:

 DECLARE @cursor CURSOR SET @cursor = CURSOR FAST_FORWARD TYPE_WARNING FOR SELECT TOP (10) ContactName FROM Customers OPEN @cursor ...... CLOSE @cursor DEALLOCATE @cursor 

CLOSE and DEALLOCATE are not strictly necessary, as the cursor variable goes out of scope at the end of the batch. However, this is a good form, as you can add more code at the end later, and you should free up resources as soon as possible.

TYPE_WARNING tells you when SQL Server implicitly converts the requested cursor type (FAST_FORWARD) to another type (usually STATIC) if the requested type is not compatible with the SELECT statement.