How best to optimize the WHERE clause in a query where several parameters MAY or may not impact the WHERE clause. For example:
SELECT * FROM TABLE
(@Parameter1 = 'ALL' OR (@Parameter1 <> 'ALL and Field1 = @Parameter1)) AND
(@Parameter2 = 'ALL' OR (@Parameter2 <> 'ALL and Field2 = @Parameter2)) AND
(@Parameter3 = 'ALL' OR (@Parameter3 <> 'ALL and Field3 = @Parameter3))
If all three parameters = 'ALL', then the query is a realatively easy (and fast) SELECT * FROM TABLE. This could be achieved by a simple IF statement, eg:
IF @Parameter1 = 'ALL' AND @Parameter2 = 'ALL' AND @Parameter3 = 'ALL' THEN SELECT * FROM TABLE
SELECT * FROM TABLE WHERE Field1 = @Parameter1 AND Field2=...
However, in reality, the "real" query has 22 potential parameters, any combination of which could be used in the query.
Can anyone reccommend a way to structure the where clause (other than using IF statements to evaluate the "ALL" condition of each potential combination of paramters) WITHOUT using dynamic SQL to build the where clause on the fly?