Ignore Null Bind Variables in SQL

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

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);