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?

3
  • AND SubDescription1 IS NULL OR SubDescription1 = @SubDesc1 Study user manual about Operator Precedence.
    – Akina
    Dec 6, 2019 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, 2019 at 10:12
  • some of the subdecriptions can be null.
    – user196572
    Dec 6, 2019 at 10:13

1 Answer 1

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 )
1
  • Thanks, Akina. It works.
    – user196572
    Dec 6, 2019 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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