Javatpoint Logo
Javatpoint Logo

SQL Server CASE

The CASE expression is a part of the control flow function that evaluates a list of conditions and gives the output when the first condition is met. It is primarily used to handle conditional statements, same as IF-THEN-ELSE statements in other programming languages. A CASE statement evaluates the condition, and when finds true, it will stop executing and return the result. If it will not find any conditions true, it evaluates the ELSE part to return the value and ends. It will give NULL value when no ELSE block is found and no conditions are met true.

The CASE expression can be used anywhere a valid program or query is used like SELECT, WHERE, and ORDER BY clause. Its main function is to manage multiple IF statements in the SELECT clause. In MS SQL Server, the CASE statement allows the user to add several conditions to perform various sets of actions.

Types of CASE Statement

There are two forms of CASE statement in MS SQL Server:

  1. Simple CASE Statement
  2. Searched CASE Statement

Simple CASE

We used the simple CASE statement for equality tests. It determines the result by comparing an expression to a set of multiple values to determine the result. In other words, this statement evaluates conditions one by one and returning the expression specified in the THEN clause when the condition and expression are matched.

The following are the syntax for Simple CASE statement:

Here the CASE statement checks for equality by comparing the expression (input_expression) to a value defined in each WHEN clause. It gives the resultant statement in the corresponding THEN clause if the input_expression matches the WHEN clause's value. If the input_expression does not match any value and the ELSE block is available, the CASE expression will return the ELSE block's result. The CASE expression will give NULL value when the ELSE part is not found, and the input_expression does not match any value in the WHEN clause.

Example

The following statement explains the simple example of a CASE statement:

It will give the below output:

SQL Server CASE

Let us explain the working of the CASE statement on tables. The following statement creates a table named Tutorials:

Next, we will add some records into this table using the below statement:

We can verify the table using the SELECT statement that displays the following data:

SQL Server CASE

The following SQL CASE statement evaluates each condition and returns a value when the first condition is met:

It will give the following output where we can see the result as per the condition specified in a CASE statement:

SQL Server CASE

Searched CASE Statement

The searched CASE statement is a more comprehensive expression evaluation format that evaluates a set of Boolean expressions to find the result. It allows us to use the comparison operators and logical operators within each Boolean expression.

The following are the syntax for a Simple CASE statement:

This statement starts by evaluating each WHEN clause's boolean_expression in the order defined in the CASE expression. If the evaluation of boolean_expression in each WHEN clause is true, this statement gives the corresponding THEN clause results. If the evaluation is false, this statement returns the result specified in the ELSE block. The CASE expression will return NULL if the ELSE block is not found and the boolean_expression evaluation is true. The END clause is optional.

Example

Let us explain the working of the CASE statement on tables. Suppose we have a table named student that contains the following data:

SQL Server CASE

The following SQL CASE statement evaluates each condition and returns a value when the first condition is met:

In the above example, we can see that first 'age' is a part of the CASE expression. After that, the value of 'age' is compared to each WHEN statement until 'age' matches with WHEN values. Here, each WHEN statement has its own Conditional Boolean expression. Until the first Boolean expression evaluates to TRUE, each Boolean expression is evaluated for TRUE/FALSE.

After execution, it will give the following output where we can see the result as per the condition specified in the CASE statement:

SQL Server CASE

Nested CASE Statement

We can also work with the nested CASE statements. The following example illustrates the nested CASE statement:

We will get the below output:

SQL Server CASE

In the above example, we are first checking if the first WHEN condition (salary > 25000) is true or not. If it is TRUE, we will enter into another WHEN condition (salary > 35000). If the second condition becomes TRUE, the student is assigned the title of 'General Manager', otherwise just 'Manager'. Finally, if no condition matches, the student is designated as 'Assistant Manager'.

CASE Statement with ORDER BY Clause

We can also use a CASE statement with an ORDER BY clause, which is used to sort the results in ascending or descending order. The following example explains it more clearly:

  • For student age greater than 17, student's salaries should come in descending order.
  • For student age less than 17, student's salaries should come in ascending order.

This condition can be defined by using a combination of ORDER BY and CASE statements. In the below statement, we have combined the ORDER BY and CASE statement and describe the sorting condition in CASE expression:

After execution of the statement, we will get the following output:

SQL Server CASE

UPDATE Statement with a CASE Statement

SQL Server also allows us to use the CASE statement with the UPDATE statement. Suppose we want to update student's ages based on CASE statement conditions. We can do this with the following condition:

  • If the student age is 22, then update to 33
  • If the student age is 17, then update to 18

We will explain this example in the above STUDENT table. We can fulfill our requirements by executing the following update command using a CASE statement:

We will get the result as per our requirements:

SQL Server CASE

Return Type

The CASE expression returns the result depending on the context where it is used. For example:

  • If it is used in the string context, it returns the string result.
  • If it is used in a numeric context, it returns the integer, float, decimal value.

Difference between Simple and Searched CASE Statement

The following comparison chart explains the main differences between Simple and Searched CASE statement:

Simple CASE Searched CASE
There is an expression between the CASE keyword and WHEN clause. For example,
CASE
WHEN Condition1 THEN Statement1 There is no expression between the CASE keyword and WHEN clause. For example,
CASE WHEN Condition1 THEN Statement1
This statement is used for a simple equality check and determines the result by comparing an expression to a set of multiple values. This statement tests the conditions for each of the "when" statements separately. It helps us to solve more complex conditions than a simple CASE.
The simple CASE statement only supports equality tests. The searched CASE statement supports any operation that returns a Boolean value with Boolean_Expression. It includes the equal and not equal to operators.

Limitations of CASE Statement

The following are the limitations of the CASE statements:

  • The CASE statement does not allow us to control the execution flow of stored procedures and functions in SQL Server.
  • The CASE statement can have several conditions in a Case statement, but it operates only in a sequential model. When one of the conditions becomes true, it stops testing further statements.
  • The CASE statement does not allow us to NULL values in a table.

Next TopicSQL Server JOINS




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA