Explain different types of CASE expression with Example.

 Posted by CGN007 on 6/29/2012 | Category: Sql Server Interview questions | Views: 2486 | Points: 40
Answer:

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'

END

FROM employee


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Jasminej on: 6/30/2012 | Points: 10
Is this statement working fine without CASE ?

Have you tested it before post ?
Posted by: CGN007 on: 7/3/2012 | Points: 10
Thanks...Its updated.

Login to post response