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)
begin
     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
end

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

Share to: