Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to iterate through a result set by using Transact-SQL in SQL Server
Article ID: 111401 - View products that this article applies to.
This article was previously published under Q111401
This article describes various methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch.
One method is the use of temp tables. With this method, you create a "snapshot" of the initial SELECT statement and use it as a basis for "cursoring." For example:
A second method is to use the min function to "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:
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 modify the temp table method to use a newly created key column. For example:
Article ID: 111401 - Last Review: December 20, 2005 - Revision: 5.3