Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


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 database.

Temp Table Approach

This is one of the most preferred methods when using as a cursor alternative. 

Declare @au_id Varchar(20)

SET ROWCOUNT 0

Select au_id, au_lname, au_fname into #Temp from authors

SET ROWCOUNT 1

Select @au_id = au_id from #Temp

While @@rowcount <> 0

Begin

            Select * from #Temp Where au_id = @au_id

            Delete from #Temp where au_id = @au_id

            Select @au_id = au_id from #Temp

End

Set Rowcount 0

Drop table #Temp

It is to be understood that the we depend on the unique ID column for looping our way through.

Min Function

This can also be taken to another level using the min function as below.

Declare @au_id Varchar(20)

Select @au_id = Min(au_id) from authors

While @au_id IS NOT NULL

Begin

            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

End

Generating 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.

Declare @au_id Varchar(20),

            @Count Int,

            @LoopCount Int

Select Identity(int,1,1) ID, au_lname, au_fname into #Temp from authors

Select @Count = @@RowCount

Set @LoopCount = 1

While @LoopCount <= @Count

Begin

            Select * from #Temp Where ID = @LoopCount

            Set @LoopCount=@LoopCount + 1

End

Drop table #Temp

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.

Conclusion

We will discuss more practical examples with code snippets in the articles to come by. Feel free to pass on your comments to us.