Sequential Numbering and Counting of Records - Sequentially Number Records by Using a Cursor
(Page 5 of 6 )
A cursor can be used to associate a sequential number with records. To use this method you would allocate a cursor, then process through each cursor record one at a time associating a record number with each record. Here is an example that does just that. This example displays the authors last and first name with a calculated recnum value for each author in the pubs.dbo.authors table where the authors last name is less than ‘G’. Each author is displayed in order by last name and first name with the first author alphabetically being assigned a recnum of 1, and for each successive author the recnum is incremented by one.
declare
@i int
declare @name varchar(200)
declare authors_cursor cursor
for select rtrim(au_lname) + ', ' + rtrim(au_fname) from pubs.dbo.authors
where au_lname < 'G'
order by au_lname, au_fname
open authors_cursor
fetch next from authors_cursor into @name
set @i = 0
print 'recnum name'
print '------ -------------------------------'
while @@fetch_status = 0
begin
set @i = @i + 1
print cast(@i as char(7)) + rtrim(@name)
fetch next from authors_cursor into @name
end
close authors_cursor
deallocate authors_cursor
Output from the cursor query looks like this:
RecNum Name
------ -------------------------------
1 Bennet, Abraham
2 Blotchet-Halls, Reginald
3 Carson, Cheryl
4 DeFrance, Michel
5 del Castillo, Innes
6 Dull, Ann
Next: Sequentially Numbering Groups of Records >>
More MS SQL Server Articles
More By Gregory A. Larsen
/ 19 
