T-SQL query results from query results

Question

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

How to do this in a T-SQL script?

Answer 1

In this case, you can use CURSOR:

 DECLARE @id INT DECLARE @name NVARCHAR(100) DECLARE @getid CURSOR SET @getid = CURSOR FOR SELECT table.id, table.name FROM table OPEN @getid FETCH NEXT FROM @getid INTO @id, @name WHILE @@FETCH_STATUS = 0 BEGIN EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name FETCH NEXT FROM @getid INTO @id, @name END CLOSE @getid DEALLOCATE @getid 

Changed to display several parameters from the table.

Answer 2

You can do something like this:

 create procedure test as BEGIN create table #ids ( rn int, id int ) insert into #ids (rn, id) select distinct row_number() over(order by id) as rn, id from table declare @id int declare @totalrows int = (select count(*) from #ids) declare @currentrow int = 0 while @currentrow < @totalrows begin set @id = (select id from #ids where rn = @currentrow) exec stored_proc @varName=@id, @otherVarName='test' set @currentrow = @currentrow +1 end END 

Answer 3

I prefer the Microsoft KB 111401 solution http://support.microsoft.com/kb/111401 .

The link refers to 3 examples:

This article describes various methods that you can use to model FETCH-NEXT cursor logic as a stored procedure, trigger, or Transact-SQL package.

 /*********** example 1 ***********/ declare @au_id char( 11 ) set rowcount 0 select * into #mytemp from authors set rowcount 1 select @au_id = au_id from #mytemp while @@rowcount <> 0 begin set rowcount 0 select * from #mytemp where au_id = @au_id delete #mytemp where au_id = @au_id set rowcount 1 select @au_id = au_id from #mytemp end set rowcount 0 /********** example 2 **********/ declare @au_id char( 11 ) select @au_id = min( au_id ) from authors while @au_id is not null begin select * from authors where au_id = @au_id select @au_id = min( au_id ) from authors where au_id > @au_id end /********** example 3 **********/ set rowcount 0 select NULL mykey, * into #mytemp from authors set rowcount 1 update #mytemp set mykey = 1 while @@rowcount > 0 begin set rowcount 0 select * from #mytemp where mykey = 1 delete #mytemp where mykey = 1 set rowcount 1 update #mytemp set mykey = 1 end set rowcount 0 

Answer 4

 DECLARE @id INT DECLARE @name NVARCHAR(100) DECLARE @getid CURSOR SET @getid = CURSOR FOR SELECT table.id, table.name FROM table WHILE 1=1 BEGIN FETCH NEXT FROM @getid INTO @id, @name IF @@FETCH_STATUS < 0 BREAK EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name END CLOSE @getid DEALLOCATE @getid 

Answer 5

try the following:

 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