, Johann Schmitz

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