July 11, 2012 at 3:16 pm
I have on sproc that is hit often which acts as a switchboard. The sproc executes other sprocs that are dynamic. There are parameters passed but for simplicity I just added this part
IF @Blah = 0 --EXEC SP1
EXECUTE [DBO].[SP1]
IF @Blah = 1 --EXEC SP2
EXECUTE [DBO].[SP2]
IF @Blah = 2 --EXEC SP3
EXECUTE [DBO].[SP3]
IF @Blah = 3 --EXEC SP4
EXECUTE [DBO].[SP4]
I know that the underlying sprocs need to change from Dynamic to normal sprocs. That way the cached plan wont change saving the recompiles and junk like that. I can do that without disturbing the Application because it will work the same way.
Can I keep this code in there without making the code directly change. This code will need to either be in code or SQL sproc layer. It is already in a sproc and I think I would like to just change the underlying queries and move along. I am looking for solid advice that pushes me either way.
Thanks.
July 12, 2012 at 8:42 am
What do you mean by Dynamic Sprocs? Depending on how you are doing the dynamic execution the execution plan could still be re-used.
I would think that you could, if the logic allows, convert the dynamic parts to standard procs and just put the execution in your if branches and the calling application wouldn't have any issues as long as either permissions are correct or ownership chaining is able to be used.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply