CASE STATEMENT :-
Case statement works like if then else.
It is a ANSII standard.
Case statements are two types :
- Simple case
- Searched case
1) sample case statement in sql server :–
- Syntax :
Case expression
When value1 then return expression1
When value2 then return expression2
——————————–
———————————
Else
Return expewssion
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
Ename Designation
Smith worker
Arun boss
Chitty bigboss
Venky employee
B employee
2) Searched case statement in sql server:-
- Syntax :
Case
When cond1 then return expr1
When cond2 then return exp2
———————
Else
Return expr
End
Difference:
- If condition is based on equal to operation then use simple case.
- If condition is based on other than equal operation {like [<,>,<=,>=]} then use “searched case”.
Example: — Select ename,sal,
Case
When sal>3000 then ‘highsal’
When sal<3000 then ‘lowsal’
Else
‘Moderate sal’
End as sal range
From emp.
Example: student table
Sno sname s1 s2 s3
If in eed like this
Sname stot savg result.
Select sname ,(s1+s2+s3) as stot
(s1+s2+s3)/3) as savg,
Case
When s1>=35 and sz>=35 and
S3>=35 then ‘pass’
Else
‘fail’
End as result
From student