CODEWITHSUNDEEP
sqlsql-serverstored-proceduresforeach
Arti
Arti

Reputation: 3071

Execute stored procedure in a loop for set of records

I want to write a stored procedure to loop through set of records and for each of the record execute another stored procedure.

Select query returning list of id's:

select id 
from abc 
where some condition

I have a stored procedure usp_get_data @id=id which I want to execute for each of the rows of the select query. I do not want to use cursor. What are the other ways I can achieve this?

foreach(item in select statement)
{
    execute stored procedure
}

The sub stored procedure will return a list of records which I will then return back from the main stored procedure

Upvotes: 0

Views: 1235

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89489

I do not want to use cursor. What are the other ways I can achieve this?

Rewirte the stored procedure to operate over a set of data. You can pass bulk data to a stored procedure using a table-valued parameter, or by loading data into a Temp table, which the stored procedure then uses.

Or use a cursor. They aren't the worst thing, and "calling a stored procedure for each row" is probably the most common legitimate use of a cursor. Also the looping-without-a-cursor solutions are all dumb and worse than using a cursor.

Upvotes: 1

Related Questions