Ignore Null Bind Variables in SQL

Question

I am trying to create a dynamic SQL Statement which can be used to filter based on several parameters. But one of the caveats is that not all parameters will be available, so could 4 variables or could be 0.

This is my attempt at creating this query (small example with only one variable):

SELECT FIRST_NAME, LAST_NAME
FROM USERS
WHERE (CASE WHEN :1 IS NULL THEN 1 = 1 ELSE FIRST_NAME = :1 END;

This however throws a compile error so I'm wondering if there is another way to implement this?

Answer 1

You can use:

SELECT FIRST_NAME, LAST_NAME
FROM USERS
WHERE (:1 IS NULL OR FIRST_NAME  = :1)
AND   (:2 IS NULL OR LAST_NAME   = :2)
AND   (:3 IS NULL OR SPECIES     = :3)
AND   (:4 IS NULL OR HOME_PLANET = :4);

Answer 2

As an alternative you could use DECODE, this would save you from having to bind each search parameter twice. Something like:

SELECT first_name, last_name
FROM users u
WHERE DECODE(:1, NULL, 1, u.first_name, 1, 0) = 1
AND DECODE(:2, NULL, 1, u.last_name, 1, 0) = 1;