T-SQL ForEach style loop

Here is a simple pattern that I came up with for executing a ForEach style loop in t-sql against a set of data in T-SQL.

declare @Enumerator table (id int)

insert into @Enumerator
select UserId
from Users
where IsActive = 1  -- your query to select a list of ids goes here

declare @id int

while exists (select 1 from @Enumerator)
     select top 1 @id = id from @Enumerator 

     exec dbo.DoSomething @id
     -- your code to do something for a particular id goes here

     delete from @Enumerator where id = @id

First, I declared a table variable that I called @Enumerator.  Then, I am inserting a list of UserId into the table variable.

The loop is set to keep looping as long as there is at least a row of data in @Enumerator.  Inside the loop, we first select the next id from @Enumerator.

Now that we have our id, we perform whatever action necessary against it and then delete if from our @Enumerator table.

The loop continues until @Enumerator is out of rows and we’re done.

Update: What About SQL Server Cursors?

The same functionality can be accomplished using cursors in TSQL.

declare @Enumerator CURSOR

select UserId
from Users
where IsActive = 1

OPEN @Enumerator

declare @id int

while (1=1)
 FETCH NEXT FROM @Enumerator into @id
 if (@@FETCH_STATUS <> 0) break
 exec dbo.DoSomething @id

CLOSE @Enumerator
DEALLOCATE @Enumerator

Here is why I don’t like to do it this way.

  1. Too many ways to screw up the declaration.  "If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option" -- MSDN
  2. "OPEN" statement.  Picking nits but I don't want to bother opening it, I just want to use it.
  3. "CLOSE" and "DEALLOCATE".  I am under the impression that since this is defined as a local cursor with a local variable, that everything will be closed and cleaned up when procedure or function completes, but not CLOSEing and DEALLOCATEing your cursors is a bad habit to get into, so I would still always specify these 2 commands when using a CURSOR

The first solution, based on a table variable, works for me.  It is something I can remember and hard for me to screw up. 
Improve Your Life
Improve Your Team
Improve Your Code
Software Projects

David Walker

David Walker is a Secure Software Consultant, a Certified Secure Software Lifecycle Professional (CSSLP), and a Professional Scrum Master. He believes in secure and reliable software and productive happy teams. He lives in Orlando with his lovely wife Lynn and his 2 dogs.