How to traverse a result set and use Transact-SQL in SQL Server

Loop result sets using Transact-SQL statements

You can use three methods to cycle a result set by using a Transact-SQL statement.

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_idChar(11)

SetRowcount0
Select* Into # mytempFromAuthors

SetRowcount1

Select@ Au_id = au_idFrom# Mytemp

While@ Rowcount <>0
Begin
SetRowcount0
Select*From# MytempWhereAu_id = @ au_id
Delete # mytempWhereAu_id = @ au_id

SetRowcount1
Select@ Au_id = au_idFrom# Mytemp <br/>
End
SetRowcount0

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_idChar(11)

Select@ Au_id = min (au_id)FromAuthors

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 **********/SetRowcount0
SelectNull mykey, * into # mytempFromAuthors

SetRowcount1
Update # mytempSetMykey =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

Original article: http://www.cnblogs.com/sskset/archive/2008/07/14/1242094.html