INF: Iterating Through a Results Set Using Transact-SQL

ID: Q111401

The information in this article applies to:

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

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
set rowcount 0

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

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

Additional query words: Windows NT 


Keywords          : kbtool SSrvTran_SQL SSrvWinNT 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 18, 1999