Knowledge Base |

# How to iterate through a result set by using Transact-SQL in SQL Server

Article ID: 111401

Article Last Modified on 12/20/2005

APPLIES TO

- 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

This article was previously published under Q111401

### IN THIS TASK

- SUMMARY

- Use Transact-SQL Statements to Iterate Through a Result Set

- REFERENCES

## SUMMARY

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.
back to the top

### 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 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:

    /********** 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 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:

    /********** 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 modify the temp table 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

back to the top

Additional query words: Loop Walk

Keywords: kbhowtomaster KB111401