SQL Server Traversal

Document directory

• Loop result set using Transact-SQL statements

Loop result set using Transact-SQL statements

You can use a Transact-SQL statement to traverse a result set in three ways.

One way is to useTempTable. In this way, you create the "snapshot" of the initial SELECT statement and use it as the basic "Pointer ". For example:

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

 2

 3declare @au_id char( 11 )

 4

 5set rowcount 0

 6select * into #mytemp from authors

 7

 8set rowcount 1

 9

10select @au_id = au_id from #mytemp

11

12while @@rowcount <> 0

13begin

14    set rowcount 0

15    select * from #mytemp where au_id = @au_id

16    delete #mytemp where au_id = @au_id

17

18    set rowcount 1

19    select @au_id = au_id from #mytemp<BR/>

20end

21set rowcount 0

The second method is to "traverse" a row of a table.MinFunction. This method captures the new row that must be greater than the unique identifier of the row currently being processed in the query after the stored procedure starts to run. For example:

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

 2

 3declare @au_id char( 11 )

 4

 5select @au_id = min( au_id ) from authors

 6

 7while @au_id is not null

 8begin

 9    select * from authors where au_id = @au_id

10    select @au_id = min( au_id ) from authors where au_id > @au_id

11end

Note:: For example 1 and 2, it is assumed that each row in the source table has a unique identifier. In some cases, there may be no unique identifier. In this case, you can modifyTempThe table method uses the newly created key column. For example:

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

 2

 3set rowcount 0

 4select NULL mykey, * into #mytemp from authors

 5

 6set rowcount 1

 7update #mytemp set mykey = 1

 8

 9while @@rowcount > 0

10begin

11    set rowcount 0

12    select * from #mytemp where mykey = 1

13    delete #mytemp where mykey = 1

14    set rowcount 1

15    update #mytemp set mykey = 1

16end

17set rowcount 0

18