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
		Select Top 1 @Id = Id From ATable Where Processed = 0

		--Do some processing here

		Update ATable Set Processed = 1 Where Id = @Id


Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

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

		Select Top 1 @Id = Id From #Temp

		--Do some processing here

		Delete #Temp Where Id = @Id


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:


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.