solving a problem with cursors

Question

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 please consider the following sample example: I want to print the information about only 10 customers.

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 look at the output:

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      

The row number 11 also has been printed. Is it a problem in a cursor or it always occurs? Is there any way not to print this addition data? Thanks (i use sql erver 2008)

Answer 1

Either...

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 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 10 left(cast(row_number() over(order by ContactName) as varchar)+ '      ', 6) + ContactName
    from Customers