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

Are you trying to do this?

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

From what I understand it looks like it.

Answer 2

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 3

Your COALESCE returns NULL when the @MiddleName parameter and the MiddleName column are both NULL, but the test will evaluate to false because a NULL does not equal any other NULL.

To workaround this you should explicitly test the @MiddleName parameter for nullity:

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

Answer 4

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)