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?

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