CASE STATEMENT IN SQL SERVER

Dept Table

Case Statement

It works like if then else

It is a Ansii standard

Case statement are of two types

1 simple case

2 searched case

1 simple case

Syntax: case expr 

When value1 then return expr1

When value2 then return expr2

else

Return expr

End

Example

select ename,

case job

when 'clerk' then 'worker'

when 'manager' then 'boss'

when 'president' then 'big boss'

else

'employee'

end as designation

from emp

Searched case

Syntax:

Case

When condition1 then return expr1

When condition2 then return expr2

Else

Return expr

End

Note: if the condition is based on equal to operator than use simple case

Or else

If the condition is based on other than equal operator like (<, >, <=, >= ,like)

select ename,sal,

case

when sal>3000 then 'highsal'

when sal<3000 then 'lowsal'

else

'moderate sal'

end as salranage

from emp

display studentname,stotal,savg,result

select sname,(s1+s2+s3)  as stot, ((s1+s2+s3)/3 ) as savg,

case

when s1>=35 and s2>=35 and s3>=35

then 'pass'

else

'fail'

end as result

from student