WHERE clause using values that could be NULL

Question

I need to do something like this:

DECLARE @firstname VARCHAR(35)
DECLARE @lastname VARCHAR(35)

SELECT *
FROM Customers 
WHERE Firstname = @firstname AND Lastname = @lastname

The problem is that @firstname and @lastname could sometimes be NULL. In those cases, the query above would fail to find matching rows because NULL is never equal to NULL.

If one of these variables is NULL, I'd like to return a match if the corresponding value in the database is also NULL. But, of course, SQL Server uses IS to compare for NULL. And if either value is NULL in the example above, it is not considered a match.

Is there any way to accomplish this?

Answer 1

Just use AND/OR logic e.g.

SELECT *
FROM Customers
WHERE ((Firstname IS NULL AND @firstname IS NULL) OR Firstname = @firstname)
AND ((Lastname IS NULL AND @lastname IS NULL) OR Lastname = @lastname);