SQL Server using CASE WHEN THEN statement

Question

I have a sample query like this:

select t1.name,t1.bday,t2.address,t2.contactnum
from table1 as t1
left join table2 as t2 on t1.p_id = t2.p_id
where (case when @qualified = '2' then t2.role is null
        case when @qualified = '3' then t2.role is not null` end)

When I execute the query an error pops up indicating:

Incorrect syntax near the keyword 'is'.

Any idea for a work around for this guys?

Thanks!

The purpose of this query is to get the null rows in the table and the non-null rows depending on the value passed on parameter @qualified.

Answer 1

Try with this:

select t1.name,t1.bday,t2.address,t2.contactnum
from table1 as t1
left join table2 as t2 on t1.p_id = t2.p_id
where (@qualified = '2' AND t2.role is null) OR (@qualified = '3' AND t2.role is not null)

I believe this syntax represents the conditional expression that you were trying to implement. However, such WHERE clause might result in a performance issues. If that would happen you should use:

IF @qualified = '2' THEN
BEGIN
    select t1.name,t1.bday,t2.address,t2.contactnum
    from table1 as t1
    left join table2 as t2 on t1.p_id = t2.p_id
    where t2.role is null
END

IF @qualified = '3' THEN
BEGIN
    select t1.name,t1.bday,t2.address,t2.contactnum
    from table1 as t1
    left join table2 as t2 on t1.p_id = t2.p_id
    where t2.role is not null
END