-1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Peter PitLock
  • 1,823
  • 7
  • 34
  • 71
  • Does this answer your question? [Is there a way to simplify a NULL compare of 2 values](https://stackoverflow.com/questions/66138082/is-there-a-way-to-simplify-a-null-compare-of-2-values) – Charlieface Oct 14 '21 at 18:49

2 Answers2

2

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.

Sami Kuhmonen
  • 30,146
  • 9
  • 61
  • 74
  • This has made me realise that I cannot use CASE WHEN when comparing against a NULL. I had to move it out of the CASE WHEN into the WHERE clause. If you do know how to kep it in the CASE WHEN please advise - it was not straight forward. – Peter PitLock Oct 14 '21 at 16:30
0

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.

forpas
  • 160,666
  • 10
  • 38
  • 76