In SQL Server, how does one use Transact-SQL to traverse a result set?

There are can use to iterate through result set by using Transact-SQL statements three methods.

One way is to useTemporaryTable. Using this method, you create a "snapshot" of the initial SELECT statement and use it as the basic "Pointer ". For example:

/********** Example 1 **********/
declare @au_id char( 11 )

set rowcount 0
select * into #mytemp from authors

set rowcount 1

select @au_id = au_id from #mytemp

while @@rowcount <> 0
begin
    set rowcount 0
    select * from #mytemp where au_id = @au_id
    delete #mytemp where au_id = @au_id

    set rowcount 1
    select @au_id = au_id from #mytemp
end
set rowcount 0
The second method is to useMinFunction. After this method is captured, the stored procedure starts to run. Assume that the new row has a unique identifier greater than the new row being processed in the query. For example:
/********** example 2 **********/ 

declare @au_id char( 11 )

select @au_id = min( au_id ) from authors

while @au_id is not null
begin
    select * from authors where au_id = @au_id
    select @au_id = min( au_id ) from authors where au_id > @au_id
end
Remarks: Example 1 and Example 2 assume that a unique identifier exists for each row in the source table. In some cases, there may be no unique identifier. In this case, you can modify the key columns to be created.TemporaryTable method. For example:
/********** example 3 **********/ 

set rowcount 0
select NULL mykey, * into #mytemp from authors

set rowcount 1
update #mytemp set mykey = 1

while @@rowcount > 0
begin
    set rowcount 0
    select * from #mytemp where mykey = 1
    delete #mytemp where mykey = 1
    set rowcount 1
    update #mytemp set mykey = 1
end
set rowcount 0
You can use the RowNumber method.