How do I filter using parameters but keep NULLs?

Question

I want to be able to pass in a list of parameters, and ignore the ones which are NULL. So that the query is in effect pretending that the filter isn't there and ignoring it. My problem is that the columns I'm filtering do accept NULLs, but if I use something like this, all the NULL fields are removed.

WHERE column = Case WHEN NULL column ELSE @parameter END

Answer 1

What about something like WHERE (@parameter IS NOT NULL AND column = @parameter) AND ... repeat as required...

Answer 2

A slightly better option is to test:

(@param IS NULL OR column = @param)

This takes advantage of SQL's built-in short-circuiting, and doesn't evaluate the column unless it should.

Answer 3

It sounds like you need to cover both the NULL and non-NULL cases for @parameter.

For each @parameter supplied you would need a where clause like this:

   ( @parameter IS NULL AND `column` IS NULL )
OR ( `column` = @parameter )

That will match if the parameter and the value are both NULL, or if both are non-NULL and have the same value.

The problem you have is that for a NULL column

@parameter = `column`

will not match when @parameter is itself NULL - this is because two NULLs are not treated as equal.