Programming in MS SQL Server 2012

CASE ... WHEN ... THEN Statement

CASE ... WHEN ... THEN statement is used to evaluates a list of conditions and returns one of multiple possible result expressions. Simple CASE expression compares an expression to a set of expressions to determine the result. Search CASE expression evaluates a set of Boolean expression to determine the result. CASE statement provides alternative by using the last statement like ELSE.
CASE can use in SELECT, UPDATE, DELETE and SET statements and IN, WHERE, ORDER BY and HAVING clauses.
Syntax

-- Syntax --

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

Input_expression
Input_express is used to evaluated when the simple CASE format is used input_expression is any valid expression.
WHEN when_expression
WHEN when_expression is a simple expression to which input_expression is compared when the simple CASE format is used when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
THEN result_expression
THEN result_expression is used to returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE result expression is any valid expression.
ELSE else_result_expression
ELSE else_result_expression is used to returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE return NULL else_result_expression is any valid expression. The data type of else_result_expression and any result_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression
WHEN Boolean_expression is used to evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
The following example describes simple CASE ... WHEN ... ELSE ... END statement.

-- Example 85 --

DECLARE @Dept_Code char(5),

@Dept_Name char(50)

SET @Dept_Code = '10'

SET @Dept_Name =

CASE @Dept_Code

WHEN '10' THEN 'System'

WHEN '20' THEN 'Commercial'

WHEN '30' THEN 'Accounts'

WHEN '40' THEN 'Marketting'

WHEN '50' THEN 'Administration'

ELSE 'No Idea'

END

SELECT 'Department Name is ' + @Dept_Name

Query Output Screen

* * * * *

Your Comment To AUTHOR