SQL Coalesce in WHERE clause

Question

I'm trying to implement optional parameters in a stored procedure that I have but I am running into a problem. Here's a simplified query to illustrate the issue:

SET ANSI_NULLS OFF

DECLARE @MiddleName VARCHAR(20);
SET @MiddleName = NULL;

SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND [MiddleName] = COALESCE(@MiddleName, [MiddleName])

When I run this query I need to get one row back because one Torres has NULL in the [MiddleName] column. But the query returns zero rows. Using IFNULL() produces the same result. From researching COALESCE, I was under the impression that NULL would be returned if all expressions are NULL. As I am not a SQL expert I assume that I am missing something, but what is it.....

Thanks in advance for any help.

Answer 1

The problem is that in sql, "WHERE Null = Null" will never return any rows since Null does not equal itself.

You have to do

SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND ( @MiddleName IS NULL OR [MiddleName] = @MiddleName )

Answer 2

You state you are looking for the query to return the row where the field MiddleName is NULL. Unfortunately (NULL = NULL) does not return true, it returns NULL.

You need something like...

SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND ([MiddleName] = @MiddleName OR @MiddleName IS NULL)