SQL Server WHERE based on CASE

Question

Wanna search for all rows where TAX = 'asd' if PARAM equals 1, else all rows where TAX <> 'asd' (every row that isn't 'asd').

Something like this:

WHERE
  TAX (
     CASE
        WHEN PARAM = '1'
           THEN = 'asd'
        ELSE <> 'asd'
     END)

It's generally a bad idea to use `case` expressions in `where` clauses. Use `AND`/`OR` constructions instead. — jarlh, Sep 11 '18 at 12:22

score 4 · Answer 1 · answered Sep 11 '18 at 11:44

4

You can add where clause with Boolean logic :

WHERE (PARAM = 1 AND TAX = 'asd') OR
      (PARAM <> 1 AND TAX <> 'asd');

If, the PARAM has NUMERIC type, then you don't need to include ' '.

answered Sep 11 '18 at 11:44

Yogesh Sharma

score 1 · Answer 2 · answered Sep 11 '18 at 11:44

1

No need for a CASE statement just use brackets and OR.

SELECT *
FROM yourtable
WHERE (TAX = 'asd' AND PARAM = '1') OR (TAX != 'asd')

answered Sep 11 '18 at 11:44

Matt

1

Beware: it will select `(TAX, PARAM) = ('fds', '1')` – Salman A Sep 11 '18 at 12:08
@SalmanA They didn't specify they wouldn't want != asd & =1 – Matt Sep 11 '18 at 12:29
The `CASE` statement (when fixed) will produce different result from what you've posted. OP is clear that if param is 1 they want asd otherwise anything other than asd. – Salman A Sep 11 '18 at 12:31

score 0 · Answer 3 · answered Sep 11 '18 at 11:45

0

SELECT *
FROm table
WHERE (TAX = 'asd' AND PARAM IN (1))
OR (TAX <> 'asd' AND PARAM NOT IN (1))

answered Sep 11 '18 at 11:45

Mazhar

score 0 · Answer 4 · answered Sep 11 '18 at 11:48

0

This can be achieved with OR, AND and some brackets:

WHERE (PARAM = 1 AND TAX = 'asd')
OR (TAX <> 'asd')

Be sure to consider cases where TAX may be NULL, unless that is explicitly prohibited by your database design.

answered Sep 11 '18 at 11:48

FiddleStix

4 Answers4