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

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
)

Answer

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')
)

Advertisement

Answer