David Walker

T-SQL ForEach style loop

by David Walker

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

SET @Enumerator = CURSOR LOCAL FAST_FORWARD FOR select UserId from Users where IsActive = 1

OPEN @Enumerator

declare @id int

while (1=1) begin FETCH NEXT FROM @Enumerator into @id if (@@FETCH_STATUS <> 0) break

exec dbo.DoSomething @id end

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. 

David Walker

David Walker is a Software Consultant, Photographer, and Digital Artist based out of Orlando, Florida, USA.

He believes in secure reliable software and productive happy teams.

More ...