查询结果上的 T-SQL 循环

小开

最佳答案

You could use a CURSOR in this case:

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

Modified to show multiple parameters from the table.

小开

You could 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 + 1)
          

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

set @currentrow = @currentrow +1
end


END

小开

My prefer solution is Microsoft KB 111401 http://support.microsoft.com/kb/111401.

The link refers to 3 examples:

This article describes various methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch.

/*********** 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

小开

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

小开

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

小开

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