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