Geek Answers Handbook

SQL Server using the CASE WHEN THEN statement

I have an example 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 executing the request, an error message appears:

Incorrect syntax next to the 'is' keyword.

Any idea to work for these guys?

Thanks!

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

+4
source share
4 answers

Try the following:

 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 is a conditional expression that you tried to implement. However, such a WHERE may lead to performance problems. If this happens 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
+5
source

Try this (not indexed)

 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
0
source

The syntax is invalid: Have a look at http://msdn.microsoft.com/en-IN/library/ms181765.aspx

You must also specify the exact requirement so that it is easy to suggest how you can use CASE.

0
source

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
0
source

More articles:


All Articles