Arbaaz Arbaaz - 2 years ago 113
SQL Question

SQL: Greater than or equal to in where clause based on case condition?

I want to select such that if

then I would want to select rows where level is equal to 1, otherwise where level is greater than 2.

select a, b, c, level
from table1
where col1 ='x' and
CASE @reporteetype WHEN 'immediate' THEN level = '2'
WHEN 'other' THEN level > 2
else 1 = 1

This is a simplified version of my scenario. I do not want to use dynamic query. I want to know if this could be done using case?

Answer Source

You can do it using combinations of AND and OR conditions:

    a, b, c, level
FROM table1
    col1 = 'x'
    AND (
        (@reporteetype = 'immediate' AND level = 2)
        OR (@reporteetype = 'other' AND level > 2)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download