Matching BIT to DATETIME in CASE statement
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.
The CASE expression (as OMG Ponies said) is mixing and matching datatypes (as you spotted), in addition you can not compare to NULL using =
or WHEN.
WHERE
(@BitInput = 0 AND DateColumn IS NULL)
OR
(@BitInput = 1 AND DateColumn IS NOT NULL)
OR
@BitInput IS NULL
You could probably write it using CASE but what you want is an OR really.
You can also use IF..ELSE
or UNION ALL
to separate the 3 cases
精彩评论