How to number the output with a CURSOR loop?

Execute the following Microsoft SQL Server T-SQL sample code in SSMS Query Editor or Query Analyzer to sequence number query output. Starting with SQL Server 2005, the ROW_NUMBER() function can be used for sequential numbeing without a special loop.

-- Number output with ROW_NUMBER() ranking function

SELECT SeqNo = ROW_NUMBER () OVER (ORDER BY au_lname, au_fname ),

       Author = left(rtrim(au_lname),28) + ', ' + left(rtrim(au_fname),20)

FROM     pubs.dbo.authors

WHERE    au_lname < 'Smith'

ORDER BY SeqNo

------------

-- Number output in WHILE loop

USE pubs

DECLARE  @FullName VARCHAR(50),

         @n        INT

DECLARE CursorAuthor CURSOR  FOR

SELECT   left(rtrim(au_lname),28) + ', ' + left(rtrim(au_fname),20)

FROM     pubs.dbo.authors

WHERE    au_lname < 'Smith'

ORDER BY au_lname,

         au_fname

OPEN CursorAuthor

FETCH NEXT FROM CursorAuthor

INTO @FullName

-- Header Print

PRINT 'SeqNo' + cast('' AS CHAR(7)) + 'Author'

PRINT '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

SELECT @n = 0

WHILE @@FETCH_STATUS = 0

  BEGIN

    SELECT @n = @n + 1

    

    PRINT cast(@n AS CHAR(7)) + rtrim(@FullName) -- TAB right

    

    FETCH NEXT FROM CursorAuthor

    INTO @FullName

  END

CLOSE CursorAuthor

DEALLOCATE CursorAuthor

GO

/*

SeqNo       Author

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1      Bennet, Abraham

2      Blotchet-Halls, Reginald

3      Carson, Cheryl

4      DeFrance, Michel

5      del Castillo, Innes

6      Dull, Ann

7      Green, Marjorie

8      Greene, Morningstar

9      Gringlesby, Burt

10     Hunter, Sheryl

11     Karsen, Livia

12     Locksley, Charlene

13     MacFeather, Stearns

14     McBadden, Heather

15     O'Leary, Michael

16     Panteley, Sylvia

17     Ringer, Albert

18     Ringer, Anne

*/