title: SQL Coalesce in WHERE clause
body: <p>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:</p>
  
  <pre><code>SET ANSI_NULLS OFF
  
  DECLARE @MiddleName VARCHAR(20);
  SET @MiddleName = NULL;
  
  SELECT * FROM [Customer]
  WHERE [LastName] = 'Torres'
  AND [MiddleName] = COALESCE(@MiddleName, [MiddleName])
  </code></pre>
  
  <p>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.....</p>
  
  <p>Thanks in advance for any help.</p>