SQL 如何为查询返回的每一行执行一次存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/886293/
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
How do I execute a stored procedure once for each row returned by query?
提问by MetaGuru
I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure once on that user_id
我有一个以某种方式改变用户数据的存储过程。我将它传递给 user_id，它就完成了。我想在一个表上运行一个查询，然后对于每个 user_id 我发现在那个 user_id 上运行一次存储过程
How would I write query for this?
我将如何为此编写查询？
回答by Steven A. Lowe
use a cursor
使用游标
ADDENDUM: [MS SQL cursor example]
附录：[MS SQL 游标示例]
declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
select field1, field2 from sometable where someotherfield is null
open cur
fetch next from cur into @field1, @field2
while @@FETCH_STATUS = 0 BEGIN
--execute your sproc on each row
exec uspYourSproc @field1, @field2
fetch next from cur into @field1, @field2
END
close cur
deallocate cur
in MS SQL, here's an example article
在 MS SQL 中，这是一篇示例文章
note that cursors are slower than set-based operations, but faster than manual while-loops; more details in this SO question
请注意，游标比基于集合的操作慢，但比手动 while 循环快；这个SO问题中的更多细节
ADDENDUM 2: if you will be processing more than just a few records, pull them into a temp table first and run the cursor over the temp table; this will prevent SQL from escalating into table-locks and speed up operation
附录 2：如果您要处理的不仅仅是几条记录，请先将它们拉入临时表并将光标移到临时表上；这将防止 SQL 升级为表锁并加速操作
ADDENDUM 3: and of course, if you can inline whatever your stored procedure is doing to each user ID and run the whole thing as a single SQL update statement, that would be optimal
附录 3：当然，如果您可以将存储过程对每个用户 ID 执行的操作内联并将整个操作作为单个 SQL 更新语句运行，那将是最佳选择
回答by KM.
try to change your method if you need to loop!
如果您需要循环，请尝试更改您的方法！
within the parent stored procedure, create a #temp table that contains the data that you need to process. Call the child stored procedure, the #temp table will be visible and you can process it, hopefully working with the entire set of data and without a cursor or loop.
在父存储过程中，创建一个 #temp 表，其中包含您需要处理的数据。调用子存储过程，#temp 表将可见，您可以处理它，希望可以处理整个数据集，而无需游标或循环。
this really depends on what this child stored procedure is doing. If you are UPDATE-ing, you can "update from" joining in the #temp table and do all the work in one statement without a loop. The same can be done for INSERT and DELETEs. If you need to do multiple updates with IFs you can convert those to multiple UPDATE FROMwith the #temp table and use CASE statements or WHERE conditions.
这实际上取决于这个子存储过程在做什么。如果您正在更新，您可以“更新自”加入#temp 表，并在没有循环的情况下在一个语句中完成所有工作。对 INSERT 和 DELETE 也可以这样做。如果您需要对 IF 进行多次更新，您可以UPDATE FROM使用 #temp 表将它们转换为多个更新，并使用 CASE 语句或 WHERE 条件。
When working in a database try to lose the mindset of looping, it is a real performance drain, will cause locking/blocking and slow down the processing. If you loop everywhere, your system will not scale very well, and will be very hard to speed up when users start complaining about slow refreshes.
当在数据库中工作时试图失去循环的心态，这是一个真正的性能消耗，会导致锁定/阻塞并减慢处理速度。如果你到处循环，你的系统将无法很好地扩展，并且当用户开始抱怨刷新缓慢时将很难加速。
Post the content of this procedure you want call in a loop, and I'll bet 9 out of 10 times, you could write it to work on a set of rows.
发布您想要循环调用的此过程的内容，我打赌 10 次中有 9 次，您可以编写它来处理一组行。
回答by u07ch
Something like this substitutions will be needed for your tables and field names.
您的表和字段名称将需要类似这样的替换。
Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)
Declare @i Int, @MaxI Int, @UserID nVarchar(50)
Insert into @TableUser
Select User_ID
From Users
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1
While @i <= @MaxI
Begin
Select @UserID = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @UserID
Select
@i = @i + 1, @UserID = null
End
回答by Dave Rincon
You can do it with a dynamic query.
您可以使用动态查询来实现。
declare @cadena varchar(max) = ''
select @cadena = @cadena + 'exec spAPI ' + ltrim(id) + ';'
from sysobjects;
exec(@cadena);
回答by Erk
Use a table variable or a temporary table.
使用表变量或临时表。
As has been mentioned before, a cursor is a last resort. Mostly because it uses lots of resources, issues locks and might be a sign you're just not understanding how to use SQL properly.
如前所述，游标是最后的手段。主要是因为它使用了大量资源，会发出锁，并且可能是您不了解如何正确使用 SQL 的标志。
Side note: I once came across a solution that used cursors to update rows in a table. After some scrutiny, it turned out the whole thing could be replaced with a single UPDATE command. However, in this case, where a stored procedure should be executed, a single SQL-command won't work.
旁注：我曾经遇到过一个使用游标来更新表中行的解决方案。经过一些，结果发现整个事情可以用一个 UPDATE 命令替换。但是，在这种情况下，应该执行存储过程，单个 SQL 命令将不起作用。
Create a table variable like this (if you're working with lots of data or are short on memory, use a temporary tableinstead):
像这样创建一个表变量（如果您正在处理大量数据或内存不足，请改用临时表）：
DECLARE @menus AS TABLE (
id INT IDENTITY(1,1),
parent NVARCHAR(128),
child NVARCHAR(128));
The idis important.
该id是很重要的。
Replace parentand childwith some good data, e.g. relevant identifiers or the whole set of data to be operated on.
替换parent和child一些好的数据，例如相关标识符或要操作的整个数据集。
Insert data in the table, e.g.:
在表中插入数据，例如：
INSERT INTO @menus (parent, child)
VALUES ('Some name', 'Child name');
...
INSERT INTO @menus (parent,child)
VALUES ('Some other name', 'Some other child name');
Declare some variables:
声明一些变量：
DECLARE @id INT = 1;
DECLARE @parentName NVARCHAR(128);
DECLARE @childName NVARCHAR(128);
And finally, create a while loop over the data in the table:
最后，在表中的数据上创建一个 while 循环：
WHILE @id IS NOT NULL
BEGIN
SELECT @parentName = parent,
@childName = child
FROM @menus WHERE id = @id;
EXEC myProcedure @parent=@parentName, @child=@childName;
SELECT @id = MIN(id) FROM @menus WHERE id > @id;
END
The first select fetches data from the temporary table. The second select updates the @id. MINreturns null if no rows were selected.
第一个选择从临时表中获取数据。第二个选择更新@id。MIN如果未选择任何行，则返回 null。
An alternative approach is to loop while the table has rows, SELECT TOP 1and remove the selected row from the temp table:
另一种方法是在表有行时循环，SELECT TOP 1并从临时表中删除选定的行：
WHILE EXISTS(SELECT 1 FROM @menuIDs)
BEGIN
SELECT TOP 1 @menuID = menuID FROM @menuIDs;
EXEC myProcedure @menuID=@menuID;
DELETE FROM @menuIDs WHERE menuID = @menuID;
END;
回答by randomsequence
Can this not be done with a user-defined function to replicate whatever your stored procedure is doing?
这不能用用户定义的函数来复制你的存储过程正在做什么吗？
SELECT udfMyFunction(user_id), someOtherField, etc FROM MyTable WHERE WhateverCondition
where udfMyFunction is a function you make that takes in the user ID and does whatever you need to do with it.
其中 udfMyFunction 是您制作的一个函数，它接收用户 ID 并执行您需要对其执行的任何操作。
See http://www.sqlteam.com/article/user-defined-functionsfor a bit more background
有关更多背景信息，请参阅http://www.sqlteam.com/article/user-defined-functions
I agree that cursors really ought to be avoided where possible. And it usually is possible!
我同意应该尽可能避免使用游标。而且这通常是可能的！
(of course, my answer presupposes that you're only interested in getting the output from the SP and that you're not changing the actual data. I find "alters user data in a certain way" a little ambiguous from the original question, so thought I'd offer this as a possible solution. Utterly depends on what you're doing!)
（当然，我的回答假设您只对从 SP 获取输出感兴趣，并且您没有更改实际数据。我发现“以某种方式更改用户数据”与原始问题有点含糊不清，所以我想我会提供这个作为一个可能的解决方案。完全取决于你在做什么！）
回答by Tom
I like the dynamic query way of Dave Rincon as it does not use cursors and is small and easy. Thank you Dave for sharing.
我喜欢 Dave Rincon 的动态查询方式，因为它不使用游标并且小而简单。谢谢戴夫分享。
But for my needs on Azure SQL and with a "distinct" in the query, i had to modify the code like this:
但是为了我对 Azure SQL 的需求以及查询中的“不同”，我不得不像这样修改代码：
Declare @SQL nvarchar(max);
-- Set SQL Variable
-- Prepare exec command for each distinctive tenantid found in Machines
SELECT @SQL = (Select distinct 'exec dbo.sp_S2_Laser_to_cache ' +
convert(varchar(8),tenantid) + ';'
from Dim_Machine
where iscurrent = 1
FOR XML PATH(''))
--for debugging print the sql
print @SQL;
--execute the generated sql script
exec sp_executesql @SQL;
I hope this helps someone...
我希望这可以帮助别人...
