Sql-server – Null check condition in Where clause

casenullsql serverwhere

DECLARE @SubDesc1 nvarchar(100)   
SET @SubDesc1 = null

I would like to do something like that.

Query 1

SELECT * 
From Table1   
where Category = 'Category1'   
  AND [Description] ='ABC'   
  AND SubDescription1 IS NULL   
   OR SubDescription1 = @SubDesc1

Query 2

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
  AND [Description] ='ABC'  
  AND SubDescription1 = (Case WHEN @SubDesc1 IS NOT NULL 
                              Then @SubDesc1
                              ELSE SubDescription1 
                              END)

None of them solved if my @SubDesc1 = 'def'

However, it will get me both records where SubDescription1 Is null and SubDescription1 = 'def'

How can I do this?

Best Answer

If NULL value of @SubDesc1 means "return all records, do not apply filtering", then 

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
  AND [Description] ='ABC'   
  AND (    SubDescription1 = @SubDesc1
        OR @SubDesc1 IS NULL)

If NULL value of @SubDesc1 means "return only those records, where the field IS NULL too", then 

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
  AND [Description] ='ABC'   
  AND (    SubDescription1 = @SubDesc1 
        OR COALESCE(@SubDesc1, SubDescription1) IS NULL )
Related Question