Reputation: 572
I have a where clause like this:
Where m.Date_6 = Case When @IsCurrentRequest = 1 Then NULL Else m.Date_6 End
@IsCurrentRequest is a bit parameter. I want to check if @IsCurrentRequest = 1 then return m.Date_6 is NULL otherwise return 1. I know this won't work because m.Date_6 = NULL is not working. How do I fix? thanks!
Upvotes: 0
Views: 34
Reputation: 6289
The =
operator will return NULL
if any of its operands is NULL
, therefore your code will not work as intended.
Try something like this:
WHERE IIF( @IsCurrentRequest = 1, IIF( m.Date_6 IS NULL, 1, 0 ), 1)
Upvotes: 1
Reputation: 5060
I am not sure I understood correctly your question, but may be this condition is what you are looking for (without using CASE)?
WHERE @isCurrentRequest=1 AND m.date_6 IS NULL OR @isCurrentRequest=0
Anyway, when checking if a value is NULL, you can't use column_name=NULL, but you should use column_name IS NULL (as NULL is a "particular" value)
Upvotes: 2