SQL Server: CASE Statement

In this post explains how to use the SQL Server (Transact-SQL) CASE statement with syntax and examples.

Description

In SQL Server (Transact-SQL), the CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement.

Syntax

The syntax for the CASE statement in SQL Server (Transact-SQL) is:

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

expression

The expression that will be compared to each of the values provided. (ie: value_1, value_2, ... value_n).

value_1, value_2, ... value_n

The values that will be used in the evaluation. Values are evaluated in the order listed. Once a value matches expression, the CASE statement will execute the corresponding statements and not evaluate any further.

condition_1, condition_2, ... condition_n

The conditions that will be evaluated. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further. All conditions must be the same datatype.

result_1, result_2, ... result_n

The value returned once a condition is found to be true. All values must be the same datatype.

Note

• If no value/condition is found to be TRUE, then the CASE statement will return the value in the ELSE clause.
• If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
• Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.
• You can not use the CASE statement to control program flow, instead, use loops and conditional statements.

Applies To

The CASE statement can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example

The CASE statement can be used in SQL Server (Transact-SQL).

You could use the CASE statement in a SQL statement as follows: (includes the expression clause)

SELECT contact_id,
CASE website_id
  WHEN 1 THEN 'AODBA.com'
  WHEN 2 THEN 'mySite.com'
  ELSE 'data.com'
END
FROM contacts;

Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)

SELECT contact_id,
CASE
  WHEN website_id = 1 THEN 'AODBA.com'
  WHEN website_id = 2 THEN 'mySite.com'
  ELSE 'data.com'
END
FROM contacts;

One thing to note is that the ELSE condition within the CASE statement is optional. It could have been omitted. Let's modify our examples with the ELSE condition omitted.

Your SQL statement would look as follows:

SELECT contact_id,
CASE website_id
  WHEN 1 THEN 'AODBA.com'
  WHEN 2 THEN 'mySite.com'
END
FROM contacts;

OR

SELECT contact_id,
CASE
  WHEN website_id = 1 THEN 'AODBA.com'
  WHEN website_id = 2 THEN 'mySite.com'
END

With the ELSE clause omitted, if no condition was found to be true, the CASE statement would return NULL.

Comparing 2 Conditions

Here is an example that demonstrates how to use the CASE statement to compare different conditions:

SELECT
CASE
  WHEN contact_id  1000 THEN 'AODBA.com'
  WHEN website_id = 2 THEN 'mySite.com'
END
FROM contacts;

Just remember that conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further. So be careful when choosing the order that you list your conditions.