CASE expression in SQL SERVER 2008

CGN007
Posted by CGN007 under Sql Server category on | Points: 40 | Views : 1488
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

Comments or Responses

Posted by: Jasminej on: 6/29/2012 Level:Starter | Status: [Member] | Points: 10
Amazing....!

Your sample statement will work without CASE ?

Have you tested it before post ?
Posted by: CGN007 on: 7/3/2012 Level:Silver | Status: [Member] | Points: 10
Thanks...
Its now Updated...

Login to post response