CODE LAB: How do I loop through a set of records in SQL Server, without using cursor?

Literally one of the most used sql queries for me. Although you should stay away from looping in SQL generally, there is still a space for it. And using Cursors are akward at the best of times

First of all you should be absolutely sure you need to iterate through each row - set based operations will perform faster in every case I can think of and will normally use simpler code.

Depending on your data it may be possible to loop just using select statements as shown below:

										
Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
		Select Top 1 @Id = Id From ATable Where Processed = 0

		--Do some processing here

		Update ATable Set Processed = 1 Where Id = @Id

End
												
										

Another alternative is to use a temporary table:

										
Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

		Select Top 1 @Id = Id From #Temp

		--Do some processing here

		Delete #Temp Where Id = @Id

End
												
										

The option you should choose really depends on the structure and volume of your data.

Note: If you are using SQL Server you would be better served using:

										
WHILE EXISTS(SELECT * FROM #Temp)
												
										

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one