sql - 循环遍历临时表的所有行并为每一行调用一个存储过程

Question

我已经声明了一个临时表来保存所有必需的值，如下所示:

    DECLARE @temp TABLE
    (
    Password INT,
    IdTran INT,
    Kind VARCHAR(16)
    )

INSERT INTO @temp
SELECT  s.Password, s.IdTran, 'test'
from signal s inner join vefify v 
    on s.Password = v.Password 
and s.IdTran = v.IdTran 
and v.type = 'DEV' 
where s.[Type] = 'start' 
AND NOT EXISTS (SELECT * FROM signal s2
            WHERE s.Password = s2.Password 
            and s.IdTran = s2.IdTran 
            and s2.[Type] = 'progress' )

INSERT INTO @temp
SELECT  s.Password, s.IdTran, 'test'
FROM signal s inner join vefify v 
    on s.Password = v.Password 
and s.IdTran = v.IdTran 
and v.type = 'PROD' 
where s.[Type] = 'progress' 
AND NOT EXISTS (SELECT * FROM signal s2
            WHERE s.Password = s2.Password 
            and s.IdTran = s2.IdTran 
            and s2.[Type] = 'finish' )
            
            
            

现在我需要循环遍历 @temp 表中的行，并为每一行调用一个 sp，它将 @temp 表的所有参数作为输入。我怎样才能实现这个目标？

Answer 1

你可以使用光标:

DECLARE @id int
DECLARE @pass varchar(100)

DECLARE cur CURSOR FOR SELECT Id, Password FROM @temp
OPEN cur

FETCH NEXT FROM cur INTO @id, @pass

WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC mysp @id, @pass ... -- call your sp here
    FETCH NEXT FROM cur INTO @id, @pass
END

CLOSE cur    
DEALLOCATE cur