SQL, BETWEEN and CASE (3 COndition Parameter)

Question

I am getting stuck on some SQL with a CASE condition.

Its a back end for a SSRS 2005 report and the user can select ALL, < 50k, or > 50K

The guts are..

SELECT LOANAMOUNT
FROM LOANS
WHERE loanAmount (CASE WHEN @Parameter 'All' THEN  = loanAmount  --Ignore the variable
ELSE @parameter = 1 THEN <= 50k
ELSE @parameter = 2 THEN > 50K
END CASE)

The issue I am having is the >< signs. I can factor in the = but I can't tell it to get ALL then change my operators in the case.

Any insights would be great. Thanks

Answer 1

All=Select All Records

or depends upon @Parameter values will be filtered

SELECT *
FROM LOANS
WHERE
 (@Parameter = 'All') OR
 (@Parameter = '1' AND loanAmount <= 50000) OR
 (@Parameter = '2' AND loanAmount > 50000) 

Answer 2

I think this will be fastest

SELECT *
FROM LOANS
WHERE
 (@Parameter = 'All') OR
 (@Parameter = '1' AND loanAmount <= 50000) OR
 (@Parameter = '2' AND loanAmount > 50000) 

Note, @Parameter must be of type string so compare to 'All', '1' and '2'.

loanAmount is a number. 50K means nothing as a number constant, use the whole number.

Answer 3

You don't need a case at all

SELECT LOANAMOUNT
FROM LOANS
WHERE @Parameter 'All'
OR (@parameter = '1' AND loanAmount <= 50000)
OR (@parameter = '2' AND loanAmount > 50000)

Answer 4

For a solution that actually uses CASE:

SELECT LOANAMOUNT
FROM LOANS
WHERE CASE WHEN @Parameter = 'All' THEN 1
        WHEN @Parameter = 1 AND loanAmount <= 50000 THEN 1
        WHEN @Parameter = 2 AND loanAmount >  50000 THEN 1
        ELSE 0
    END = 1