SQL Switch/Case in where clause with NULL value

Question

My question is very similar to SQL Switch/Case in where clause.

I get parameters. If it is excpected that value is NULL, I need to get all the values, otherwise I need to filter with value.

I'm trying to do this in SQL:

    WHERE param 
      CASE
       WHEN #{value} IS NULL THEN {IS NOT NULL}
       ELSE =#{value}
      END

How should I correct this SQL request?

       WHERE param IS NOT NULL // all values if **value** is NULL
       WHERE param = #{value}  // i.e. value = 'data'

'param' is some column in a table, 'value' I get before this request. If value = null I shuold get all values, If for examle value = 'data' I need to filter. — A user, Oct 17 '18 at 13:21
if the value is null then return all params that are not null? — Yahel, Oct 17 '18 at 13:32
Yes, if the value is null then return all params that are not null. — A user, Oct 17 '18 at 22:07

score 1 · Accepted Answer · answered Oct 17 '18 at 13:09

1

This is normally done with logic such as this:

where #{value} is NULL or #{value} = param

I assume #{value} is the value being passed in and param is some column in a table.

answered Oct 17 '18 at 13:09

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Oct 17 '18 at 13:36

0

If I understand you, if the passed parameter is null then return all rows that the column is not null and if it has value then the ones that has the value. Then you should use:

where param is NOT NULL OR param = #{value}

answered Oct 17 '18 at 13:36

Yahel

546
5
6

In this case I get all values. – A user Oct 17 '18 at 23:13

score -1 · Answer 3 · answered Dec 05 '19 at 18:59

-1

i came across similar need. found a solution. thanks to a article on https://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic

e.g. declare @param select * from blabla where ( @param is null or blabla.bla_fld = @param )

answered Dec 05 '19 at 18:59

Anish Kutti

337
2
7

SQL Switch/Case in where clause with NULL value

3 Answers3