Loop through T-SQL Result Set
In this article I would concentrate on the various
looping mechanisms available using Transact SQL. I do have mentioned the
use of Curosrs as evil in my "Server
2000 Best Practices" article. But the very next question asked to me
immediately is, then how do I loop through a resultset without using Cursor.
Here are some of the alternatives in your disposal.
Note : All the examples discussed uses the Pubs
Temp Table Approach
This is one of the most preferred methods when using
as a cursor alternative.
au_id, au_lname, au_fname into #Temp from authors
@au_id = au_id from #Temp
@@rowcount <> 0
Select * from #Temp Where au_id = @au_id
Delete from #Temp where au_id = @au_id
Select @au_id = au_id from #Temp
It is to be understood that the we depend on the
unique ID column for looping our way through.
This can also be taken to another level using the min
function as below.
@au_id = Min(au_id) from authors
@au_id IS NOT NULL
Select au_id, au_lname, au_fname from authors Where au_id = @au_id
Select @au_id = min(au_id) from authors where au_id > @au_id
In both the methods discussed we have capitalized on
the fact that we use the unique ID available to us in the table. But what
happens if we donot have one to use. This really gets a bit trickier. We alter
our temp table to generate the same for us.
Identity(int,1,1) ID, au_lname, au_fname into #Temp from authors
@Count = @@RowCount
@LoopCount = 1
@LoopCount <= @Count
Select * from #Temp Where ID = @LoopCount
Set @LoopCount=@LoopCount + 1
You can also use the method one discussed earlier to
do this by dynamically creating our number. But I feel this is more of a
programmers approach to the simple problem.
We will discuss more practical examples with code
snippets in the articles to come by. Feel free to pass on your comments to us.