SQL Coalesce in a WHERE clause

Question

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

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 return one row, because one Torres has NULL in the [MiddleName] column. But the query returns null rows. Using IFNULL () gives the same result. From the COALESCE study, I got the impression that NULL would be returned if all the expressions were NULL. Since I'm not an SQL expert, I assume that I am missing something, but what is it .....

Thanks in advance for your help.

Answer 1

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

You have to do

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

Answer 2

You state that you are looking for a query to return a string where the MiddleName field 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) 

Answer 3

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 this.

Answer 4

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

To get around this, you should explicitly check the @MiddleName parameter for nullity:

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