selective filtering, part i

what if you only want to filter a select statement if a non-null value is passed to your query as a parameter? you have a couple of options. one is to use dynamic sql to build your WHERE clause and only include the filter if the parameter value is not null. however, as i will attempt to establish as law at one point or another, dynamic sql is bad and should be avoided at all costs. from my experience, the easiest and best way to accomplish this without resorting to dynamic sql is to make use of the ISNULL function like so:

WHERE ...
AND a.column_a = ISNULL(@param1, a.column_a)

a.column_a = a.column_a is always true if a.column_a does not contain nulls, thus if a null is passed to @param1, the statement will not be filtered. if the column can contain nulls, we have to be a be more creative depending on what you want to accomplish. what about using the SET ANSI_NULLS OFF option? this allows for the logical comparison of nulls (i.e. null = null evaluatues to true). doing so makes the example above return all rows when @param1 is null. if you do not want rows returned where a.column_a is null, simply add an additional line:

WHERE ...
AND a.column_a = ISNULL(@param1, a.column_a)
AND a.column_a IS NOT NULL

problem solved. one last thing i should note is my example is likely a much simpler example than what you have since i am only concerned with one column; you must take caution to make sure your other comparisons will not return undesired results when using SET ANSI_NULLS OFF.