CASE SQL Server function

How do I use the CASE function in SQL Server (Transact-SQL)? In SQL Server (Transact-SQL) CASE operator has functionality of IF-THEN-ELSE operator. You can use a CASE statement in an SQL sentence.

Syntax of CASE function in SQL

Syntax of CASE statement in SQL Server (Transact-SQL):

CASE expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
…
WHEN value_n THEN result_n
ELSE result
END

OR

CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
WHEN condition_n THEN result_n
ELSE result
END

Parameters or arguments of the CASE function

• expression – an expression that will be compared to each of the provided values. (that is: value_1, value_2, ... value_n).
• value_1, value_2, … value_n are the values to be compared. The values are compared in the order indicated. When value expression matches, the CASE operator will execute the specified commands and no longer compare value.
• condition_1, condition_2, … condition_n are the conditions to be compared. The conditions are compared in this order. Once the condition is true, the CASE operator will return the result and will not compare the conditions further. All conditions must be the same type.
• result_1, result_2, … result_n are returned values after the condition is set to true. All values must be of the same data type.

Note

• If value/condition is not defined as TRUE, the CASE operator will return the value in the ELSE operator.
• If ELSE is omitted and no condition is true, the CASE operator will return NULL.
• The conditions are compared in this order. Once the condition is true, the CASE operator will return the result and will not compare the conditions further.
• You cannot use a CASE operator to control program flow, instead use loops and conditional operators.

Application
The CASE statement can be used in future versions of SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example
Let’s look at some examples of SQL Server CASE statement to see how to use CASE statement in SQL Server (Transact-SQL). For example:

SELECT shop_id,
CASE market_id
WHEN 1 THEN 'IKEA'
WHEN 2 THEN 'METRO'
ELSE 'EMAG'
END
FROM markets;

Or you can write an SQL sentence using the CASE statement as follows: (except for the expression)

SELECT shop_id,
CASE
WHEN market_id = 1 THEN 'IKEA'
WHEN market_id = 2 THEN 'METRO'
ELSE 'EMAG'
END
FROM markets;

It should be noted that the ELSE condition in the CASE operator is optional. It could have been lowered. Let’s change our examples if the ELSE condition is omitted.

Your SQL statement will look like this:

SELECT shop_id,
CASE market_id
WHEN 1 THEN 'IKEA'
WHEN 2 THEN 'METRO'
END
FROM markets;

OR

SELECT shop_id,
CASE
WHEN market_id = 1 THEN 'IKEA'
WHEN market_id = 2 THEN 'METRO'
END
FROM markets;

If ELSE is omitted and if the condition was not defined as true, the CASE operator returns NULL.

Comparison of two conditions

Here is an example showing how to use a CASE operator to compare different conditions:

SELECT
CASE
WHEN market_id < 100 THEN 'IKEA'
WHEN shop_id = 2 THEN 'METRO'
END
FROM contacts;

Just remember that the conditions are compared in the specified order. Once the condition is true, the CASE operator will return the result and will not compare the conditions further. So be careful when choosing the order in which your conditions are listed.

CASE в Transact-SQL

The CASE expression can be used in almost any T-SQL instruction where it is possible to use valid expressions such as: SELECT, UPDATE, WHERE, SET and even in ORDER BY.

CASE has two so-called formats:

• A simple CASE expression is a simple comparison of a value (expression) with a set of other values (expressions);
• Search expression CASE – in this case CASE contains a set of logical expressions that are calculated to return the result.

CASE syntax in Transact-SQL

Simple CASE expression

CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

Search expression CASE

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

Description of parameters:

• input_expression – an expression to be checked in a simple CASE format;
• WHEN when_expression is an expression with which input_expression is compared in the case of a simple format. The data type when_expression must be the same as input_expression or at least implicitly converted;
• THEN result_expression is an expression that will be returned if the current condition is met;
• ELSE else_result_expression – an additional parameter ELSE, which is designed for cases when none of the conditions listed in CASE has been met. This is an optional parameter. If ELSE is not specified and conditions are not met, NULL will return;
• WHEN Boolean_expression is a logical expression used in the CASE search format that is used to calculate the result. This is a kind of test condition and there may be several such conditions.

CASE returns the result of the first expression (THEN result_expression) whose condition was met, i.e. WHEN returns TRUE. Thus, if CASE contains several equivalent WHEN conditions which will return TRUE, the result (specified in THEN) of the first expression will be returned.

The data type of the result returned by the CASE expression will correspond to the highest priority data type from the type set in the result_expressions and else_result_expression expressions.

SQL CASE