Meidi
Meidi

Reputation: 572

Where clause check parameter value return a field is NULL

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

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

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

etsa
etsa

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

Related Questions