I think I have asked this question before but I could not find the thread so I can post additional questions there. So please forgive me if I have to start a new one. The issue is about sproc performance. I asked from that thread about this structure: CREATE PROC mySproc @intMode int AS IF @intMode =1 SELECT * FROM tblX ELSE IF @intMode =2 SELECT * FROM tblY ELSE SELECT * FROM tblY (Again) Would this sproc be recompiled everytime the code execution path changes due to changes in @intMode values. That is, say the sproc was compiled for @intMode=1 then compiled again(?) for @intMode=2, does calling mySproc for @intMode=1 causes it to recompile or sql will use the previous execution plan? Does sql creates 3 execution plan for this query? If I'll create sproc for each of the select statements, would it give me a faster mySproc, since the queries on all path will be compiled and stay compiled even if @intMode changes? I know I can try this myself, and I'm about to do that, but I need your expertise to verify my tests. Thanks in advance!