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.
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.