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

I have declared a temp table to hold all the required values as follows:

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

    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' )

Now i need to loop through the rows in the @temp table and and for each row call a sp that takes all the parameters of @temp table as input. How can I achieve this?

现在我需要遍历@temp表中的行，并且对于每一行调用一个sp，它将@temp表的所有参数作为输入。我怎样才能做到这一点？

4 个解决方案

#1

you could use a cursor:

你可以使用游标：

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

#2

Try returning the dataset from your stored procedure to your datatable in C# or VB.Net. Then the large amount of data in your datatable can be copied to your destination table using a Bulk Copy. I have used BulkCopy for loading large datatables with thousands of rows, into Sql tables with great success in terms of performance.

尝试将数据集从存储过程返回到C＃或VB.Net中的数据表。然后，可以使用批量复制将数据表中的大量数据复制到目标表中。我已经使用BulkCopy将具有数千行的大型数据表加载到Sql表中，并且在性能方面取得了巨大成功。

You may want to experiment with BulkCopy in your C# or VB.Net code.

您可能希望在C＃或VB.Net代码中试验BulkCopy。

#3

something like this?

这样的事情？

DECLARE maxval, val, @ind INT;
SELECT MAX(ID) as maxval FROM table;

while (ind <= maxval  ) DO           

      select `value` as val from `table` where `ID`=ind;

      CALL fn(val);

      SET ind = ind+1;
end while;

#4

You always don't need a cursor for this. You can do it with a while loop. You should avoid cursors whenever possible. While loop is faster than cursors.

你总是不需要光标。你可以用while循环来完成它。你应该尽可能避免使用游标。虽然循环比游标快。

#1