• No, the if-else will not cause a recompile.

    When SQL compiles the procedure the first time, it will compile and optimise all of the queries in the procedure. Even ones in branches of if/else that will not be executed on that run. It does the optimisation based on the parameter values for that first run.

    This can, in some cases, lead to poor query plans for some branches of the if/else that will hinder performance for some executions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass