Refactoring a Stored Procedure that has too many IF statements
I have a Stored Procedure that has grown into quite a mess over time. It began from a user need to pass parameters, and return differently filtered result sets based on the valued of the parameters. So, For a SP with parameters A and B, the pseudo code looked something like: @A varchar @B varchar If (A = SomeValue and B = SomeValue) begin Select X,Y,Z where D=E and F=G End If(A = SomeOtherValue and B = SomeOtherValue) begin Select X,Y,Z where D=N and F=O End This worked fine and was easy to manage with just a couple parameters. However, over time, more and parameters have been added so the different combinations have grown in to a few dozen IF statements. I am trying to find a way to refactor this into something more manageable, but not sure even how to begin. Would maybe Case Selects be a good way to go here or, would that end up being just as complex?
If I understood your question correctly, this is not the best way to approach this situation. Keep in mind that SP compiles the execution plan with the first parameters that you will provide. Say If you run this SP with these pair (A = SomeValue and B = SomeValue) and compile the execution plan. Say, this pair returns 10 rows back! Now you have a plan in cache. The next time you run your SP with these If(A = SomeOtherValue and B = SomeOtherValue) parameters using the cached plan which suppose to return only 10 rows but in reality returns 10,000. The performance will degrade! Try modularizing you root SP, calling smaller stored procedure from the root one. It's hard to tell without knowing more information about your data. Also you could run your procedure using all kinds of different combinations of parameters with EXEC YOURSP WITH RECOMPILE in your development environment to see if you get different execution plans, then you could adjust your code accordingly. You might also consider using OPTION(RECOMPILE) on the statement level but in your case, I don't think it will help because you have the IF logic in the body of SP itself.