如何为查询返回的每一行执行一次存储过程?

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?

我该如何编写查询?

6 个解决方案

#1

203

use a cursor

使用游标

ADDENDUM: [MS SQL cursor example]

附录:[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循环要快;更多细节，请提问。

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

ADDENDUM 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

ADDENDUM 3:当然，如果您可以内联存储过程对每个用户ID的操作，并将整个事件作为一个SQL update语句运行，那将是最优的。

#2

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 FROM with the #temp table and use CASE statements or WHERE conditions.

这实际上取决于这个子存储过程在做什么。如果您正在更新，您可以“更新”到#temp表，并在一个语句中完成所有的工作，而不需要循环。插入和删除也可以这样做。如果您需要使用IFs进行多次更新，您可以使用#temp表和用例语句或在什么情况下将这些更新转换为多个更新。

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次，你可以把它写在一组行上。

#3

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

#4

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-functions for a bit more background

请参阅http://www.sqlteam.com/article/userdefinedfunctions以获得更多的背景信息。

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中获得输出感兴趣，并且不更改实际数据。我发现“以某种方式改变用户数据”与最初的问题有点模糊，所以我认为这是一个可能的解决方案。完全取决于你在做什么!

#5

You can do it with a dynamic query.

您可以使用动态查询来完成它。

declare @cadena varchar(max) = ''
select @cadena = @cadena + 'exec spAPI ' + ltrim(id) + ';'
from sysobjects;
exec(@cadena);

#6

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...

我希望这能帮助某人…

#1

203