while for fetch_status cursores tsql

tsql - for - sql server cursor fetch



T-SQL loop sobre los resultados de la consulta (5)

Mi solución preferida es Microsoft KB 111401 http://support.microsoft.com/kb/111401 .

El enlace se refiere a 3 ejemplos:

Este artículo describe varios métodos que puede usar para simular una lógica FETCH-NEXT similar a un cursor en un procedimiento almacenado, desencadenador o lote de 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<BR/> 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

select @id=table.id from table una consulta select @id=table.id from table y necesito pasar el select @id=table.id from table por los resultados para poder ejecutar un procedimiento de almacenamiento para cada exec stored_proc @varName=@id,@otherVarName=''test'' filas exec stored_proc @varName=@id,@otherVarName=''test''

¿Cómo puedo hacer esto en un script de T-SQL?


Podrías hacer algo como esto:

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


Puede usar un CURSOR en este caso:

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

Modificado para mostrar múltiples parámetros de la tabla.


prueba esto:

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


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