• The procedure's execution plan has to generate a plan for all the DML statement that can be used regardless of the value of parameters and variables. Since the first case statement has 4 different select statement, the query plan for this procedure should include query plan for each one of those queries. During runtime only one of the select statements will run. The second case statement has only one select statement that each time will get the same records, but each time will show a different column. For this type of statement the server can create a single query plan, because regardless of the columns that it will show at the end, it will use the same algorithm in order to get the data.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/