tsql - for - sql server cursor fetch

T-SQL loop sobre los resultados de la consulta

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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