August 23, 2001 at 12:25 pm
If I test an incoming value to a stored procedure and execute a If block, will there be an compiled execution plan stored for each block If? Do you still reap the benefit of the stored procedure? For Example:
If @test-2 = 1 Begin Select * From Table1 End
If @test-2 = 2 Begin Select * From Table2 End
If @test-2 = 3 Begin Select * From Table3 End
August 23, 2001 at 4:40 pm
Yes, I believe you will still see the benefits of the compilation. Pretty easy to test - build it, run once so it can compile and cache the data, then run again, see if the run time changes. You could also use Profiler to see if a recompile op occurs.
That said, what you're illustrating is not my favorite technique. You'd be far better off (in my opinion!) to do the branch in code and call 3 different procs. This is really just a form of dynamic sql.
Of course, compilation is not the only thing you gain from using procs - you also gain a valuable separation of code that you can modify without rebuilding your app.
Andy
December 8, 2002 at 4:40 pm
No. SS created the compiled plan based on the 1st execution of the proc. Your queries will for tables 2 and 3 (depending on order) will use the query for table 1. To gain optimal SP benefit, you need to make this query a "driver" proc that calls one of 3 separate procs based on the input. The same holds true for any Stored proc that conditionally executes statements.
December 8, 2002 at 4:54 pm
Wow, old thread. Not sure I agree, but will have to test to see I guess. Worst case you could use with recompile, though obviously not a great solution.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply