• Jeaux wrote: "My question is why couldn't you rewrite the example..." I have a different answer to this question. I once had a stored procedure with a similar structure to the one you gave as an example. The problem was that (if I understand correctly) SQL Server comes up with an optimized plan for the first query it runs the first time it compiles.

    For example: Let's say SQL Server first compiles and optimizes based on the first query. Later a user runs the proc and the IF takes runs the second query. Now it is possible that the second query runs like a turtle-sooo sloowwwwww.

    That's the exact problem I had once, and it almost brought my server down. I had a bunch of users running the same proc but hitting one of 5 queries that weren't optimized. I turned it into one master proc calling 6 different baby procs. Voila! Problem solved. It had nothing to do with writing clean code. It was a matter of optimization. Fair disclosure (but any bad explanations are mine): I got this trick off of Kimberly Tripp's site.