Ciclo T-SQL sui risultati della query

select @id=table.id from table una query select @id=table.id from table e ho bisogno di eseguire il loop dei risultati in modo da poter eseguire una procedura di archiviazione per ogni riga exec stored_proc @[email protected],@otherVarName='test'

Come posso farlo in uno script T-SQL?

In questo caso potresti usare un CURSORE:

 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 @[email protected], @otherVarName='test', @[email protected] FETCH NEXT FROM @getid INTO @id, @name END CLOSE @getid DEALLOCATE @getid 

Modificato per mostrare più parametri dalla tabella.

Potresti fare qualcosa del genere:

 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 @[email protected], @otherVarName='test' set @currentrow = @currentrow +1 end END 

La mia soluzione preferita è Microsoft KB 111401 http://support.microsoft.com/kb/111401 .

Il collegamento si riferisce a 3 esempi:

In questo articolo vengono descritti vari metodi che è ansible utilizzare per simulare una logica FETCH-NEXT simile a un cursore in una stored procedure, trigger o batch Transact-SQL.

 /********** 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
 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 @[email protected], @otherVarName='test', @[email protected] END CLOSE @getid DEALLOCATE @getid 

prova questo:

 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