asked on

Conditioning in the WHERE clause

Hello.

Let say I have a date field and it can be NULL. I query this table in my store procedure and depending on the parameter, I want to select either all values WITH NULL and WITHOUT NULL, only values WITH NULL, or only values WITHOUT NULLS.

I tried to put CASE and IF into my WHERE clause but apparently it's either not supposed to be working there or I had a wrong syntax. Anyway, my preudo-script is:

SELECT * FROM my_table
WHERE
CASE @UseNulls
WHEN 1 THEN ( my_date IS NULL )
WHEN 0 THEN ( my_date IS NOT NULL )
WHEN 2 THEN ?? /* to have both */ ??

END

Thanks,
Mik

Thanks

ASKER CERTIFIED SOLUTION

Einstine98

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Einstine98

Hey... just had another though (which I always do :-) )

SELECT * FROM my_table
WHERE
CASE
WHEN My_Date IS NULL THEN '1'
ELSE '0'
END = @UseNulls
OR @UseNulls = 2

SOLUTION