CODEWITHSUNDEEP
sqlsql-server
mpeterb
mpeterb

Reputation: 115

SQL Coalesce in WHERE clause

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.

Upvotes: 10

Views: 64216

Answers (4)

LukeH
LukeH

Reputation: 269368

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)

Upvotes: 2

gcores
gcores

Reputation: 12656

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.

Upvotes: 2

MatBailie
MatBailie

Reputation: 86706

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)

Upvotes: 4

David
David

Reputation: 34563

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 )

Upvotes: 19

Related Questions