Sql based on dynamic where condition

Question

I have a query, in this there are multiple conditions based on parameter

select a,b,c,d from xyz where case p='Closed' then (q-r)=0 case p='Pending' then (q - isnull(r,0) )>0 case P='All' then all records to be display

It gives Incorrect Syntax near line case

Kindly suggest me any other solution to change 'where condition' depending on parameter value,

since in given sql, 'where' condition is depend on parameter p value.

You can see in given syntax, if the parameter value is change, condition also get changed.

In first case if p='Closed' then select ... where q-isnull(r,0)=0

if p='Pending' then select ... where (q - isnull(r,0) )>0

In first condition it is comparing 'equal to' condition & in next example it is 'greater than' zero value.

like in third condition I want to display all records.

Here problem is, 'case' structure is only return a value, not expression.

Answer 1

If you want multiple criteria to be evaluated in your case statement then all you have to do is shift them all to before the THEN and return a value that indicates that it passes the check.

For your code, this can be done like so:

select a,b,c,d from xyz where 1 =
case p='Closed' and (q-r)=0 then 1
case p='Pending' and (q - isnull(r,0) )>0 then 1
case P='All' then 1

Though your syntax doesn't work with SQL Server (I'm not sure what database you're querying). A working version in SQL Server would be:

select a,b,c,d from xyz where 1 =
CASE 
    WHEN p='Closed' AND (q-r)=0 THEN 1
    WHEN p='Pending' AND (q - isnull(r,0) )>0 THEN 1
    WHEN P='All' THEN 1
    ELSE 0 
END