SQL param in WHERE clause to fetch NULL value

Question

Declare @param char(1)
SET @param = 'A'

@param can be 'A','B' or 'C'

SELECT x, y FROM myTbl
WHERE y = @param

if @param = 'A' or 'B' then y = @param works fine if @param = 'C' then I want to WHERE to fetch the rows where y IS NULL

I could achieve this using dynamic sql but can't figure out how to do it without dynamic sql. Any thoughts?

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Oct 28, 2016 at 20:56

Gordon Linoff · Accepted Answer · 2016-10-28 20:39:18Z

1

Use basic boolean logic:

SELECT x, y
FROM myTbl
WHERE y = @param or (@param = 'C' and y is null);

If you really want to be more explicit:

SELECT x, y
FROM myTbl
WHERE (@param <> 'C' AND y = @param) OR
      (@param = 'C' AND y IS NULL);

answered Oct 28, 2016 at 20:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1