Sometimes (e.g. in maintenance/migration scripts) you have to iterate over a result set because of an unusal table structure. Unfortunaly, T-SQL does not provide a built-in function to accomplish this. But, with a little trick, you can use the While-Statement to create a for-each loop.
DECLARE @cnt int -- current counter in the loop DECLARE @cntMax int-- max idx of the loop DECLARE @id int -- initialize vars SELECT @cnt=1; SELECT @cntMax = Count(*) FROM MyTable WHILE @cnt <= @cntMax BEGIN --While Loop WITH DataQuery AS ( SELECT row_number() OVER (ORDER BY x.ID) AS Row, x.* FROM MyTable x ) SELECT @id = (SELECT dq.ID FROM DataQuery dq WHERE Row=@cnt) -- execute your statements here. The query above selects the ID column of the current row SELECT @cnt = @cnt+1 END
However, using this method creates a big performance impact, because it uses at least n+1 queries to your database. So don't use it in production scripts!.