September 2, 2011 at 7:57 am
If-statements in queries could cause some sub-optimal execution plans. See the following for reference: http://sqlinthewild.co.za/index.php/2010/12/14/do-if-statements-cause-recompiles/
My suggestion would be to have separate procs if the queries are that different and it makes sense to do so.
You can nest stored procedures up to a max of 32-levels according to BOL (http://msdn.microsoft.com/en-us/library/ms190607.aspx). I would be cautious not to go overboard with the nesting though, as it could make troubleshooting very cumbersome and difficult.
I don't think the nesting will have any impact on performance, but you have to keep in mind what might happen if one of the nested sp's fail...you should probably think of encapsulating the calls in explicit transactions.
Hope this helps.
September 2, 2011 at 9:00 am
In general, in these situations, I nest stored procedures. It's not like nesting views or functions, so performance should be fine. Check the execution plans of your queries to be sure they're working well though. That's completely independent from the nesting question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply