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.

What are trying to achieve by using case when? You placed is null and is not null incorrectly in your query. — Moha Dehghan
– Moha Dehghan, Commented Feb 13, 2013 at 8:53
If the parameter @qualified has a value of two I would like to get all the null rows in table2 and if it has a value of three I would like to get all the non null rows in table2 — jr17
– jr17, Commented Feb 13, 2013 at 8:57
Please explain the purpose of this query as it is not a correct query. — Joshi
– Joshi, Commented Feb 13, 2013 at 8:57
CASE in T-SQL can only return a single, atomic value - not an expression / code block. — marc_s
– marc_s, Commented Feb 13, 2013 at 10:43

Ivan Golović · Accepted Answer · 2013-02-13 09:11:50Z

6

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

edited Feb 13, 2013 at 9:11

answered Feb 13, 2013 at 8:53

Ivan Golović

8,8223 gold badges27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CloudyMarble · Accepted Answer · 2013-02-13 08:57:37Z

0

Try this (Untested)

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 @qualified
        WHEN '2' THEN t2.role is null
        WHEN '3' THEN t2.role is not null 
    END

answered Feb 13, 2013 at 8:57

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Comments

Ravi Maggon · Accepted Answer · 2013-02-13 09:02:26Z

0

Syntax is not correct: Please look at http://msdn.microsoft.com/en-IN/library/ms181765.aspx

Also post you exact requirement so that its easy to suggest how you can use CASE.

answered Feb 13, 2013 at 9:02

Ravi Maggon

838 bronze badges

Comments

TechDo · Accepted Answer · 2013-02-13 09:11:30Z

0

Please try:

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 t2.role is null then '2' else '3' end)=@qualified

answered Feb 13, 2013 at 9:11

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL Server using CASE WHEN THEN statement

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related