CODEWITHSUNDEEP
sqlnullwhere-clauseparam
SilverFish
SilverFish

Reputation: 1106

SQL param in WHERE clause to fetch NULL value

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?

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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

Upvotes: 1

Related Questions