CASE expression in SQL SERVER 2008

CASE
Evaluates a list of conditions and returns one result.

There are two types of CASE expressions in SQL

1.Simple CASE
The simple CASE expression compares an expression to a set of simple expressions to determine the result.
2.Searched CASE
The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

--Simple CASE expression:

CASE input_expression



         WHEN when_expression THEN result_expression [ ...n ]



         [ ELSE else_result_expression ]



    END

--Searched CASE expression:

CASE



         WHEN Boolean_expression THEN result_expression [ ...n ]



         [ ELSE else_result_expression ]



    END

Examples:



--Simple CASE expression:

SELECT empID,empname,CASE gender



          WHEN 'M' THEN 'Male'



          WHEN 'F' THEN 'Female'



          ELSE 'NA'



        END



        FROM employee

--Searched CASE expression:

SELECT empID,empname,

          CASE 

          WHEN gender= 'M' THEN 'Male'



          WHEN gender= 'F' THEN 'Female'



          ELSE 'NA'

        END



     FROM employee