Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.Edit: Actually the temporary table #test is different when created from different sessions.
Yup i totally get that. However if you carefully look at the sproc we are not even hitting the condition i.e If Debug=1 because we are executing the sproc as exec dbo.Test_Sproc @Debug=0 , why would it matter what is inside the If condition if we are not even hitting that condition?