Execute stored procedure for all the result set without using cursor

Question

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.

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?

Answer 1

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

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

Answer 2

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.

Answer 3

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

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.

Answer 4

You'll have to convert your proc to a 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)

Answer 5

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.