1
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?

  • AND SubDescription1 IS NULL OR SubDescription1 = @SubDesc1 Study user manual about Operator Precedence. – Akina Dec 6 '19 at 10:04
  • What does you need when a variable is null - all records or only those where the field is null too? – Akina Dec 6 '19 at 10:12
  • some of the subdecriptions can be null. – user196572 Dec 6 '19 at 10:13
0

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 )
|improve this answer|||||
  • Thanks, Akina. It works. – user196572 Dec 6 '19 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.