John Hick-456673 (4/17/2013)
Comments posted to this topic are about the item <A HREF="/articles/T-SQL/97908/">The Elusive Conditional WHERE Clause</A>
As long as there is always the same column used then it is fine and won't need a recompile.
However this part:
or (7=@pUseCondition and d1.[WeekDay] in ('tue','thu'))
or (8=@pUseCondition and [color=red]d.[Date][/color]>=@pEarliestDate)
or (9=@pUseCondition and d1.[WeekDay] in ('mon','fri'))
sticks out and on big data sets might show the use of a wrong query plan. It's these parts that make dynamic SQL with an forced recompile more useful (but harder to evaluate and maintain). And where a recompile hint becomes useful.
The advantage is that the code is written in one block and thus easier to maintain and test (in my opinion) - which in itself is a good thing. The statement is all there unlike in dynamic SQL which is only known (and maybe shown) at execution.