Conditioning in the WHERE clause
Posted on 2006-07-02
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
WHEN 1 THEN ( my_date IS NULL )
WHEN 0 THEN ( my_date IS NOT NULL )
WHEN 2 THEN ?? /* to have both */ ??