DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE