INF: Iterating Through a Results Set Using Transact-SQL

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

It is often desirable to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch. For instance, it might be necessary to process all the rows in a table sequentially in order to avoid filling up the transaction log. This article will discuss various methods for accomplishing this task.

MORE INFORMATION

SQL Server 4.2x does not implement server side cursoring. However, it is possible to iterate through a results set using Transact-SQL statements.

One method is by using temp tables. This creates a "snapshot" of the initial select and uses that as a basis for the "cursoring." The following example illustrates how to do this:

/********** example 1 **********/

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

A second method is by using the min() function to "walk" a table one row at a time. This method would catch new rows that had been added after the stored procedure began execution, provided that the new row had a unique identifier greater than the current row being processed in the query.

/********** example 2 **********/

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

NOTE: Examples 1 and 2 both assume that a unique identifier exists for each row in the source table. In some cases, no unique identifier may exist. If this is the case, the temp table method may be modified to use a newly created key column. Example 3 illustrates this method.

/********** example 3 **********/

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