Im Artikel "Implementing Dynamic WHERE-Clause in Static SQL" wird beschrieben wie man mittels COALESCE, ISNULL oder CASE eine dynamische WHERE-Klausel implementieren kann. Wenn ich mir die Beispiele ansehe, dann habe ich aber meine Zweifel, ob der Optimizer da noch eine Chance hat den Zugriff performant (über den richtigen Index) hinzubekommen.
Spätestens bei großen Datenmengen dürfte der Einsatz von echt dynamischem SQL mittels EXEC() schneller sein. Das wird ganz gut im Artikel "Introduction to Dynamic SQL (Part 2)" erklärt.