"case when in where condition logic" Code Answer

15

You want boolean logic:

select name, address, phone 
from users 
where
    (@viewtype = 'AR' and phone is null)
 or (@viewtype = 'PO' and phone is not null)
 or @viewtype not in ('AR', 'PO')

If 'AR' and 'PR' are the only possible values of the parameter, then the last predicate can be removed.

For performance with such parameterized query, consider using option(recompile), so that a new plan is generated for the current value value of the parameter.

By Aks.. on May 26 2023

Answers related to “case when in where condition logic”

Only authorized users can answer the search term. Please sign in first, or register a free account.