SQL 遍历临时表的所有行并为每一行调用一个存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22618968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop through all the rows of a temp table and call a stored procedure for each row
提问by merazuu
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 表的所有参数作为输入。我怎样才能做到这一点？
回答by Zdravko Danev
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
回答by dgsjr
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。
回答by ejectamenta
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;
回答by Durgesh Pandey
You can do something like this
你可以做这样的事情
Declare @min int=0, @max int =0 --Initialize variable here which will be use in loop
Declare @Recordid int,@TO nvarchar(30),@Subject nvarchar(250),@Body nvarchar(max) --Initialize variable here which are useful for your
select ROW_NUMBER() OVER(ORDER BY [Recordid] ) AS Rownumber, Recordid, [To], [Subject], [Body], [Flag]
into #temp_Mail_Mstr FROM Mail_Mstr where Flag='1' --select your condition with row number & get into a temp table
set @min = (select MIN(Rownumber) from #temp_Mail_Mstr); --Get minimum row number from temp table
set @max = (select Max(Rownumber) from #temp_Mail_Mstr); --Get maximum row number from temp table
while(@min <= @max)
BEGIN
select @Recordid=Recordid, @To=[To], @Subject=[Subject], @Body=Body from #temp_Mail_Mstr where Rownumber=@min
-- You can use your variables (like @Recordid,@To,@Subject,@Body) here
-- Do your work here
set @min=@min+1 --Increment of current row number
END
回答by Sam
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 循环来完成。您应该尽可能避免使用游标。While 循环比游标快。
