0

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

Arti
  • 2,993
  • 11
  • 68
  • 121
  • Does this answer your question? [SQL Call Stored Procedure for each Row without using a cursor](https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor) – bendataclear May 13 '20 at 12:28
  • 4
    Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s May 13 '20 at 12:31
  • 1
    You know a loop is not really that much different from a cursor? What reason do you have for not using a cursor? – Nick.Mc May 13 '20 at 12:33
  • @Nick.McDermaid, because cursors take up memory and create locks. – Arti May 13 '20 at 12:39
  • @bendataclear here in my case id will not be sequential, so I don't this I can use similar query as answered for the post you tagged. – Arti May 13 '20 at 12:42
  • 1
    @Arti You mean you need it to run asynchronously? – bendataclear May 13 '20 at 12:46
  • 1
    @Arti so does a loop that reads from a table every iteration – Nick.Mc May 13 '20 at 12:46

1 Answers1

1

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 1
    Another option, depending on what the sproc does, is to write it as a table-valued function that can be `CROSS APPLY`'d. This isn't always possible, but often overlooked. – Jeroen Mostert May 13 '20 at 12:54
  • 1
    If cursors were as bad as some people believe, they would not have been implemented! – MJH May 13 '20 at 13:00
  • Also cursors are old. They are basically the same as they were 25 years ago, when servers ran single-core 100MHz processors. What was "too slow" then is hardly relevant any more. – David Browne - Microsoft May 13 '20 at 13:23