Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardlaw
Yak Posting Veteran
68 Posts |
 Posted - 2013-05-17 : 15:37:57
|
| HiI've created a SP that returns a row of data based on an ID provided.I'd now like to run the SP for each ID within another table, but ideally without using a loop.Thoughts/ideas?Thanks as always |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-17 : 15:56:00
|
| The ideal way would be to modify the stored proc (or create another stored proc) which joins the result set produced by the original stored proc to the table that contains the ID's.Other than that, you would have to loop. When you think about it, that makes sense because the stored procedure is a unit of compilation - which produces a single result, and so joining with that cannot be done in a set-based manner. |
 |
|
|
richardlaw
Yak Posting Veteran
68 Posts |
Posted - 2013-05-17 : 16:43:46
|
| Thanks.What would be best in this case (from a performance perspective) - a join with the SP, or a simple while loop? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-17 : 16:45:48
|
| If you join with the CODE IN THE STOREDPROC which results in a set-based query, that would most likely give you much better performance than a while loop. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-17 : 17:56:50
|
| Agree 100% with James. Of course, if you need a more specific answer, show the proc. |
|
|
|
|
|
|
Run an SP against a table of IDs without looping