CASE Expression in WHERE

[adobe]

I know you should be able to use a Case Expression within a WHERE Clause but I am having some trouble.

The user will be able to select what type of Operator they want so they can search client balance information...because of this I need 1 clause for the BETWEEN and one for all the others (>=, =, <= , ect). I am receiving error at line 7 "Incorrect syntax near the keword THEN which doesn't help much.

DECLARE @Operator VARCHAR(10)
SET @Operator = '>'

SELECT Balance
FROM ClientInformation
WHERE @Operator = CASE WHEN 'BETWEEN'
THEN CAST(Balance AS MONEY) BETWEEN 1 and 2
ELSE CAST(Balance AS MONEY) @Operator 30
END

Can someone help me out here

[ck9663]

Try something like this:

 
WHERE (@Operator = 'BETWEEN' and CAST(Balance AS MONEY) BETWEEN 1 and 2) or
 (@Operator = '>'and CAST(Balance AS MONEY) > 30
) or
(@Operator = '<'and CAST(Balance AS MONEY) < 30
)
 
 

Happy Coding!!!

~~ CK