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?

`where (:1 IS NULL OR FIRST_NAME = :1) AND (:2 etc` – jarlh Aug 17 '21 at 21:22 — jarlh, Aug 17 '21 at 21:22

score 2 · Accepted Answer · answered Aug 17 '21 at 21:47

2

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

answered Aug 17 '21 at 21:47

MT0

143,790
11
59
117

This should work even if FIRST_NAME, LAST_NAME, etc. are null themselves right? This should still return those records as well? – Chen Aug 17 '21 at 22:22
@Chen If `:1` is `NULL` then it will match any `FIRST_NAME` regardless of if it is `NULL` or non-`NULL`. – MT0 Aug 17 '21 at 22:23
Yep did a quick test and it worked, thank you very much. – Chen Aug 17 '21 at 22:26

score 0 · Answer 2 · answered Aug 18 '21 at 05:02

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;

Ignore Null Bind Variables in SQL

2 Answers2