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
* Disclaimer. Since free code is available out there on the internet, and so many people have written up about the use of it I decided to use some examples from a source I trust. And in this line I would like to thank them and everyone else that shares there code, for this and reference them in this regard.