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:

CASE <input_expression>  

     WHEN Condition1 THEN Statement1  

     WHEN Condition2 THEN Statement2  

     .  

     .  

     WHEN ConditionN THEN StatementN  

     ELSE Statement     

END

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:

SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END AS "CASE Result";  

It will give the below output:

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

CREATE TABLE Tutorials (    

  tutorial_id int IDENTITY(1,1) PRIMARY KEY,  

  tutorial_name varchar(45) NOT NULL,    

  price float    

);

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

INSERT INTO Tutorials (tutorial_name, price)     

VALUES ('SQL', 20000.00),   

('Java', 25000.00),   

('Python', 30000.00),   

('MS SQL', 20000.00),     

('Android', 35000.00);

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

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

SELECT tutorial_id, tutorial_name,  

CASE tutorial_name  

    WHEN 'SQL' THEN 'SQL is developed by IBM'  

    WHEN 'Java' THEN 'Java Java was created at Sun Microsystems Inc.'  

    WHEN 'MS SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'  

    ELSE 'Do not Know.'  

END AS Description  

FROM Tutorials;

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

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:

CASE  

  WHEN boolean_expression_1 THEN result_1  

  WHEN boolean_expression_2 THEN result_2  

  .  

  .  

  WHEN boolean_expression_N THEN result_N  

  ELSE else_result  

END;

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:

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

SELECT Id, name, age,  

CASE  

 WHEN age>22 THEN 'Mature'  

 WHEN age>=17 AND age<=22 THEN 'Immature'  

 ELSE 'Child'  

END AS Maturity FROM STUDENT;

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:

Nested CASE Statement

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

SELECT Id, name, salary,  

CASE  

 WHEN salary>25000 THEN  

  CASE  

   WHEN salary> 35000 THEN 'General Manager'  

   ELSE 'Manager'  

  END  

 ELSE 'Assistant Manager'  

END AS Designation  

FROM STUDENT;

We will get the below output:

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:

SELECT Id, name, age, salary  

 FROM STUDENT  

 ORDER BY CASE  

WHEN age>17 THEN salary End DESC,  

CASE WHEN age<17 THEN salary    

END

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

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:

UPDATE STUDENT   

SET age = CASE age  

 WHEN 22 THEN 23   

 WHEN 17 THEN 18   

 WHEN 29 THEN 30  

 WHEN 16 THEN 15  

 ELSE 25  

 END

We will get the result as per our requirements:

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.