Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » System FunctionsRSS Feeds

CASE Expression

Posted By: Sarita Patel     Category: SQL Server     Views: 4292

This article explains about 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 when_expression is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid SQL Server expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

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.

Syntax of Searched CASE Expression :

CASE
    WHEN Boolean_expression THEN result_expression 
     [ ...n ] 
     [ 
        ELSE else_result_expression 
     ] 
END

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.  
  
Share: 

 
 
 

Didn't find what you were looking for? Find more on CASE Expression Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of CASE Expression is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!