CODEWITHSUNDEEP
mysqlparametersnull
Susan Lax
Susan Lax

Reputation:

How do I filter using parameters but keep NULLs?

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

Upvotes: 0

Views: 424

Answers (2)

jpj625
jpj625

Reputation: 1138

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.

Upvotes: 1

codemonkey
codemonkey

Reputation: 2665

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

Upvotes: 0

Related Questions