Calling a proc for each row in a table without using cursor

Question

Calling a proc for each row in a table without using cursor

SQLCurious

SSCrazy

Points: 2339
More actions
April 14, 2015 at 12:24 pm

#295987

I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient? Please advice.
Thanks in advance,
SQLCurious

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SQLCurious (4/14/2015)
I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient? Please advice.
Thanks in advance,
SQLCurious

This sort of depends on what the proc does. Could you modify the proc so it doesn't have to do this one row at a time? If so you might be able to leverage table valued parameters so you can call your proc once and only once for the entire set.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLCurious SSCrazy Points: 2339 More actions · Answer 2

No, I can not replace the 2nd proc's code into the first one, for the second one is a stand alone. In fact, there is a whole slew of procs that get called serially afterwards.

However, I really liked your tip of using TVPs. I am considering making this change to the entire set of procs. Even though it means an overhaul of bigger proportion involving application changes, I think it is definitely worth considering. Now I only wish that the application architect approves of this proposal. 🙂

Thanks, again, for your valuable tip. Irrespective of whether I can implement in this particular scenario or not, it definitely opened my eyes for a newer approach which, I think, will help me immensely in future. I appreciate your help.

SQLCurious