| Author |
 Topic  |
|
|
richardlaw
Yak Posting Veteran
United Kingdom
59 Posts |
 Posted - 02/27/2013 : 06:47:07
|
I’m trying to make my SP’s as efficient as possible (from a speed perspective).
I’m running a loop (while) statement, and part of the logic within the loop needs to get data from another table based on the logic of the loop. Should I create another SP and run that within the loop, or is it better to add the code into the loop.
It will make the code longer if I add it in, and there will be duplication, but is it faster this way?
Thanks as always |
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/27/2013 : 07:48:08
|
The ideal way would be to avoid the loop altogether and do that operation in a set-based query. Except in a few cases, such loops can be reformulated to run as a set-based query. If you can post the code with some sample input data and the desired output, I or someone else on the forum can help to make it set-based, if that is possible.
Regarding your original question, it is almost always better to not create a stored procedure and instead add the code in-line even though that makes the code longer. |
 |
|
|
richardlaw
Yak Posting Veteran
United Kingdom
59 Posts |
Posted - 02/27/2013 : 08:53:14
|
quote:
Originally posted by James K
The ideal way would be to avoid the loop altogether and do that operation in a set-based query. Except in a few cases, such loops can be reformulated to run as a set-based query. If you can post the code with some sample input data and the desired output, I or someone else on the forum can help to make it set-based, if that is possible.
Regarding your original question, it is almost always better to not create a stored procedure and instead add the code in-line even though that makes the code longer.
Thank you so much - that really helps. I'm new to working with SP's, so I'll research set-based queries first.
Thanks again - much appreciated. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/27/2013 : 09:13:13
|
| You are very welcome. Please post back if you need additional help. |
|
|
| |
Topic |
|
Inline SP's or add the code?
