T-SQL loop over query results

user_308079 0
Viewed 173970

I run a query select @id=table.id from table and I need to loop over the results so I can exec a store procedure for each row exec stored_proc @varName=@id,@otherVarName='test'

How can I do this in a T-SQL script?

tsql
6 Answers
DECLARE @id INT
DECLARE @filename NVARCHAR(100)
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT top 3 id,
filename 
FROM  table

OPEN @getid
WHILE 1=1
BEGIN

    FETCH NEXT
    FROM @getid INTO @id, @filename
    IF @@FETCH_STATUS < 0 BREAK

    print @id

END


CLOSE @getid
DEALLOCATE @getid
user_98500700

try this:

declare @i tinyint = 0,
    @count tinyint,
    @id int,
    @name varchar(max)

select @count = count(*) from table
while (@i < @count)
begin
    select @id = id, @name = name from table
    order by nr asc offset @i rows fetch next 1 rows only

    exec stored_proc @varName = @id, @otherVarName = 'test', @varForName = @name

    set @i = @i + 1
end
user_29540700
Related