Posted By: Ray Vega
Let’s say I have the following simple table variable:
declare @databases table ( DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases
Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?
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 using just
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)
COUNT will have to touch every single row in the table, the
EXISTS only needs to touch the first one (see Josef’s answer below).