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-02-27 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 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
68 Posts |
Posted - 2013-02-27 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-27 : 09:13:13
|
You are very welcome. Please post back if you need additional help. |
|
|
|
|
|
|
|