Jon Saltzman (5/20/2010)
I second the sommarskog.se site (which gretchkosiy 2550 refers to above) - that is an excellent writeup which includes gotchas for SQL 2008 SP1 and dynamic SQL if I recall correctly.
It is the definitive work - everyone decrying the use of dynamic SQL absolutely must read it.
...suffering because of the complete failure of the SQL query engine to short-circuit effectively...
This is not an optimiser failure, it is a design choice. The optimiser's goal is to quickly produce a good plan that can be reused by future queries. It does include some contradiction detection, and the CASE statement can be used where evaluation-order guarantees are required.
In general, the benefits of allowing the optimiser freedom to reorder expressions and logical operations greatly outweighs the perceived drawbacks of not implementing short-circuiting.
Again, as of 2008 SP1 CU5, OPTION (RECOMPILE) can be used to provide some of the aims of short-circuiting, at the cost of not caching a plan and incurring a full recompilation on every execution.