I'm attempting to create a T-SQL case statement to filter a query based on whether a field is NULL
or if it contains a value. It would be simple if you could assign NULL
or NOT NULL
as the result of a case but that doesn't appear possible.
Here's the psuedocode:
WHERE DateColumn = CASE @BitInput
WHEN 0 THEN (all null dates)
WHEN 1 THEN (any non-null date)
WHEN NULL THEN (return all rows)
From my understanding, the WHEN 0
condition can be achieved by not providing a WHEN
condition at all (to return a NULL
value).
The WHEN 1
condition seems like it could use a wildcard character but I'm getting an error regarding type conversion. Assigning the column to itself fixes this.
I have no idea what to do for the WHEN NULL
condition. My internal logic seems to think assigning the column to itself should solve this but it does not as stated above.
I have recreated this using dynamic SQL but for various reasons I'd prefer to have it created in the native code.
I'd appreciate any input. Thanks.