CASE STATEMENT :-

Case statement works like if then else.
It is a ANSII standard.
Case statements are two types  :

  1. Simple case
  2. 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