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



* * * * *


Email Your Comment To AUTHOR