Search code examples
sqlsql-serverwhere-clause

CASE WHEN in WHERE condition logic

I have query like below

SELECT name, address, phone
FROM users
WHERE
    CASE @ViewType
    WHEN 'AR' THEN phone IS NULL
    WHEN 'PO' THEN phone IS NOT NULL
    ELSE 1=1 END

ViewType is parameter, but I received an error in my SQL.

Solution

  • 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.