CASE Expression

CASE expression in sql server with examples.

CASE expression enables many forms of conditional processing to be placed into a SQL statement. By using CASE, more logic can be placed into SQL statements instead of being expressed in a host language or 4GL program.

CASE is a deterministic i.e They return same value every time they are called with a specific set of values.

CASE expression has 2 formates as listed below.

• Simple CASE function compares  an expression to a set of simple expressions.
• Searched CASE function evaluates a set of boolean expression .  

Syntax of Simple CASE Expression :

CASE input_expression 

    WHEN when_expression THEN result_expression 

     [ ...n ] 

     [ 

        ELSE else_result_expression 

     ] 

END

input_expression is any valid sql server expression evaluated when using the simple .

WHEN Boolean_expression is a Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

n is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression is a expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid SQL Server expression.

ELSE else_result_expression is a expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. 

else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

Examples of CASE Expression :

Example 1 : Use of simple CASE expression function in select clause 

SELECT 

   CASE SUBSTRING('Sintax-Example',1,2)

           WHEN 'Si' THEN 'Please correct your spelling. Don''t use si instead of sy. It is Syntax-                                                                       Example.'

            WHEN 'Se' THEN 'Please correct your spelling. Don''t use se instead of sy. It is Syntax-                                                                     Example.'

         ELSE 'Syntax-Example'

  END

Output

Please correct your spelling. Don't use si instead of sy. It is Syntax-Example.

Above example compares first 2 characters specified string and displays output based on comparision expression evaluates to true.  

Example 2 : Use of searched CASE expression function in select clause

SELECT 

             CASE 

                      WHEN SUBSTRING('Sintax-Example',1,2) = 'Si' THEN 'Please correct your spelling.                                                                Don''t use si instead of sy. It is Syntax-Example.'

                      WHEN SUBSTRING('Sintax-Example',1,2) = 'Se' THEN 'Please correct your spelling.                                                                Don''t use se instead of sy. It is Syntax-Example.'

                      ELSE 'Syntax-Example'

             END

Output

Please correct your spelling. Don't use si instead of sy. It is Syntax-Example.

Above example compares first 2 characters specified string and displays output based on comparision expression evaluates to true.  

CASE Expression