不使用游标，对所有结果集执行存储过程

If I want to execute stored procedure using the values returned from result set of a select statement. So number of times SP should get executed is equal to the number of result set from the select statement.

如果我想使用从select语句的结果集返回的值来执行存储过程。因此，SP应该执行的次数等于select语句中的结果集的数量。

Is there any other way than using a cursor to do the above?

除了使用光标执行上述操作之外还有其他方法吗？

UPDATE
Can anyone please give sample code with While loop at least?

更新任何人都可以请至少使用While循环提供示例代码吗？

5 个解决方案

#1

In T-SQL there are only 2 ways for iteration. While loop or cursors. If you don't want to use cursors, you had to use while loop as James Wiseman said.

在T-SQL中，只有两种迭代方式。循环或游标。如果您不想使用游标，则必须使用while循环，如James Wiseman所说。

ANother way to accomplish this situation is SQL CLR. If you are using SQL CLR, you can use all C# (or VB.Net) iterations to reach your goal.

另一种实现这种情况的方法是SQL CLR。如果您使用的是SQL CLR，则可以使用所有C＃（或VB.Net）迭代来实现目标。

#2

I would convert the proc to use a table variable and pass the data set in using that. The beauty of this is that once you have made the change, you can use the same proc for either single row inserts or mulitple and do it in sets not row-by-row. You need SQL Server 2008 for this one.

我会转换proc以使用表变量并使用它传递数据集。这样做的好处在于，一旦完成了更改，就可以对单行插入或多行使用相同的proc，并且可以在不逐行的集合中执行。您需要SQL Server 2008才能实现此目的。

#3

You'll have to convert your proc to a Multi-Statement Tabled Value UDF..

您必须将proc转换为Multi-Statement Tabled Value UDF ..

create function dbo.udf_Whatever_That_Proc_Did(
     @SameOldParam as int
)
AS Begin
Declare --same variables here

/*same code in your proc that does not 
    - invoke nondeterministic built-in function
    - change state of database
    - return messages to caller
*/

Return
End

To utilize function:

利用功能：

Select * 
from dbo.udf_Whatever_That_Proc_Did(9999)

#4

An alternative to a cursor is a while loop, which is sometimes recommended as an alternative to SQL Cursors.

游标的替代方法是while循环，有时建议将其替换为SQL游标。

Is the problem that you want to avoid using the cursor, or is it that you are wating to avoid iteration altogether?

是您要避免使用光标的问题，还是您要避免完全避免迭代？

#5

Maybe this could help you, create a UDF and then call the stored proc from within that UDF. Since you can call UDF in a select query, it should execute the stored proc as many times as you have results in select query.

也许这可以帮助你，创建一个UDF，然后从该UDF中调用存储的proc。由于您可以在select查询中调用UDF，因此它应该执行存储过程多次，因为您在select查询中有结果。

#1