There are 2 questions on SO, but they are different and do not solve this problem. Feel free to test it before marking this as duplicate.
There is a SQLFiddle for this question.
In this example, the cell phone number may be NULL
| ID | Name | Cell |
|---|---|---|
| 1 | John | 123 |
| 2 | Sally | NULL |
The query works when the cell number is not null:
DECLARE @Cell NVARCHAR(100) = '123'
SELECT *
FROM Temp
WHERE Cell = CASE WHEN @Cell IS NULL THEN NULL ELSE @Cell END
The same query fails when the cell number is null.
DECLARE @Cell NVARCHAR(100) = NULL
SELECT *
FROM Temp
WHERE Cell = CASE WHEN @Cell IS NULL THEN NULL ELSE @Cell END
The question is how to get the CASE WHEN working for both NULL and when it is comparing an actual value. Note that this is a simplified example of the real problem (which has a lot more conditions and additional complexity) and the focus is to get the example working by modifying the CASE WHEN in order to solve the real problem.
CodePudding user response：
NULL isn’t equal to anything, including NULL but you can just check if something is NULL
WHERE (@Cell IS NULL AND Cell IS NULL) OR Cell = @Cell
Probably could also move the comparison inside CASE but this is clear in meaning at least.
CodePudding user response：
You can do it without the CASE expression, with COALESCE():
DECLARE @Cell NVARCHAR(100) = ?
Select * from Temp
WHERE Cell = @Cell OR COALESCE(Cell, @Cell) IS NULL;
Replace ? with the value that you search for or NULL.
See the demo.
CodePudding user response：
The Solution is Simple, Just put an ISNULL or COLAESCE on both sides of your condition.
DECLARE @Cell NVARCHAR(100) = '123'
Select * from Temp
WHERE ISNULL(Cell,'') = COALESCE(@Cell,Cell,'')
The challenge you are facing here is that in SQL server NULL values does not act the way you think it works NULL <> NULL (Depends on ANSI NULL settings, but this is the default)
ie; if you match one NULL with another, it will always be False. So put an ISNULL and give some default values on both sides like above. and it should work
for more detail http://msdn.microsoft.com/en-us/library/ms188048.aspx