Method to override a cursor traversal result level in a T-SQL-from a http://support.microsoft.com/kb/111401

Summary

This article describes varous methods that you can
Use to simulate a cursor-like fetch-Next logic in a stored procedure,
Trigger, or transact-SQL batch.

Use Transact-SQL statements to iterate through a result set

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

One method is the useTemp
Tables. With this method, you create a "snapshot" of the initial select
Statement and use it as a basis for "cursoring." 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 <br/>
End
Set rowcount 0
 

A second method is to useMinFunction
"Walk" A table one row at a time. This method catches new rows that
Were added after the stored procedure begins execution, provided that
The new row has a unique identifier greater than the current row that
Is 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
 

Note: Both Example 1 and 2 assume that a unique
Identifier exists for each row in the source table. In some cases, no
Unique identifier may exist. If that is the case, you can modifyTempTable method to use a newly created key column. 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
 

Applies
 

•	Microsoft SQL Server 2000 Standard Edition
•	Microsoft SQL Server 4.21a Standard Edition
•	Microsoft SQL Server 6.0 Standard Edition
•	Microsoft SQL Server 6.5 Standard Edition
•	Microsoft SQL Server 7.0 Standard Edition
•	Microsoft SQL Server 2005 Standard Edition
•	Microsoft SQL Server 2005 Developer Edition
•	Microsoft SQL Server 2005 Enterprise Edition
•	Microsoft SQL Server 2005 express Edition
•	Microsoft SQL Server 2005 workgroup Edition