Returning IS NOT NULL in SQL CASE WHEN expression

Question

How can I return IS NOT NULL on from a SQL Server CASE statement?

I have tried something like this:

WHERE 
    a.BillToCustomerID IS NOT NULL
    AND CASE @taxSchedRequiered
           WHEN 1 THEN a.TaxScheduleID IS NOT NULL
        END

but it is not working for me.

Update

I need to filter by a.TaxScheduleID IS NOT NULL, just in the specific case when the value of the variable @taxSchedRequiered (possible values are: null, 0, 1) is 1.

Your question is not very clear. Perhaps this can point you in the right direction anyway: where 'TRUE' = case when @taxSchedRequired = 1 and a.TaxScheduleID is not null then 'TRUE' else 'FALSE' end and a.BillToCustomerID is not null — avery_larry
– avery_larry, Commented Nov 13, 2019 at 18:50
CASE in T-SQL is an expression (like a+b - not a statement) that can return one, atomic value (from several possible values) - but you cannot use CASE to conditionally/optionally add/run code snippets like you're trying to do..... — marc_s
– marc_s, Commented Nov 13, 2019 at 18:51
@avery_larry you are right, I edit the question, trying to be more clear. — jczmog
– jczmog, Commented Nov 13, 2019 at 19:07

JamieD77 · Accepted Answer · 2019-11-13 19:08:34Z

1

just change the logic and use an OR

WHERE 
    a.BillToCustomerID IS NOT NULL
    AND (@taxSchedRequiered <> 1 OR a.TaxScheduleID IS NOT NULL)

this will include all records if @taxSchedRequiered = 0 else it will only include records that have a a.TaxScheduleID <> NULL

Also make sure that @taxSchedRequired has a value other than NULL when you execute this

Edit to check for null param

WHERE 
    a.BillToCustomerID IS NOT NULL
    AND (ISNULL(@taxSchedRequiered,0) = 0 OR a.TaxScheduleID IS NOT NULL)

edited Nov 13, 2019 at 19:08

answered Nov 13, 2019 at 18:53

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jczmog Over a year ago

the variable taxSchedRequiered will just tell me if I have to filter by a.TaxScheduleID IS NOT NULL, or not.

JamieD77 Over a year ago

@jczmog above should do that

Collectives™ on Stack Overflow

Returning IS NOT NULL in SQL CASE WHEN expression

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related