How to structure this case when expression in the where clause?

Question

I have a parameter where there are three available options, "ecom", "nonecom", and "both".

When the parameter is set to "ecom", I want there to be a where clause where it says Customer_Number = 'ecom2x'

When it is set to "nonecom", Customer_Number <> 'ecom2x'

And when it is set to "both", it should ignore customer number altogether

How would I do this? This is what I tried:

WHERE
1 = 1
AND (
  CASE WHEN @CustomerType = 'ecom' THEN OD.Customer_Number = 'ecom2x' END
  CASE WHEN @CustomerType = 'nonecom' THEN OD.Customer_Number <> 'ecom2x' END
  CASE WHEN @CustomerType = 'both' THEN END
)

Possible duplicate of [Use case in where clause](https://stackoverflow.com/questions/46977510/use-case-in-where-clause) — SMor, Sep 12 '19 at 21:02

score 2 · Accepted Answer · answered Sep 12 '19 at 21:03

A case expression can only return a value, it doesn't allow you to select an expression.

I think you just want regular and/or logic e.g.

(
  (@CustomerType = 'ecom' and OD.Customer_Number = 'ecom2x')
  or (@CustomerType = 'nonecom' and OD.Customer_Number <> 'ecom2x')
  or (@CustomerType = 'both')
)

How to structure this case when expression in the where clause?

1 Answers1