What is case when in SQL Server? How to use Case in SQL Server? Let's find out with Quantrimang.com!
If learning programming, definitely don't ignore SQL Server. This is a definite area you should know if you want to grow in this industry. In fact, learning SQL Server is not difficult. You just need to master the basic commands. In this article, we will learn together about CASE in SQL Server.
Case SQL Server is part of a control flow function that evaluates a list of conditions and provides results when the first condition is met. It is mainly used to handle conditional statements, similar to the IF-THEN-ELSE statement in other programming languages. A CASE statement evaluates this condition, and when found true, it stops running and returns the result. If it does not find the true condition, it evaluates the ELSE part to return the value and terminates. It will give NULL value when ELSE block is not found and condition is met.
CASE statement in SQL Server can be used anywhere a valid program or query is used such as SELECT, WHERE and ORDER BY clauses. Its main function is to manage multiple IF statements in the SELECT statement. In MS SQL Server, the CASE command allows users to add a number of conditions to perform different groups of tasks.
In SQL Server, you have two basic types of CASE statements:
- SIMPLE CASE – Simple CASE statement
- Searched CASE – Searched CASE command
In there:
- Simple CASE is comparing an expression with a set of simple expressions to determine the result.
- Searched CASE is the evaluation of a set of Boolean expressions to determine the result.
- Both formats above support the ELSE argument (but are not required).
Syntax
To use the CASE statement in SQL Server, we use the following syntax:
Simple CASE
CASE bieuthuc_dauvao
WHEN bieuthuc_1 THEN ketqua_1
WHEN bieuthuc_2 THEN ketqua_2
...
WHEN bieuthuc_n THEN ketqua_n
ELSE ketqua_khac
END
Hoặc Searched CASE
CASE
WHEN dieukien_1 THEN ketqua_1
WHEN dieukien_2 THEN ketqua_2
...
WHEN dieukien_n THEN ketqua_n
ELSE ketqua_khac
END
Parameter:
- bieuthuc_dauvao: The expression will be compared against each value provided.
- bieuthuc_1, bieuthuc_2, bieuthuc_n: The expressions will be used to compare one by one with the input expression. When an expression matches bieuthu_dauvao, CASE executes subsequent statements and does not compare any further.
- dieukien_1, dieukien_2, dieukien_n: conditions are considered and approved in listed order. When a condition is determined to be true, CASE returns the result and does not evaluate further conditions. All conditions must be of the same data type.
- ketqua_1, ketqua_2, ketqua_n: the result returned after considering the condition is true. All values must be the same data type.
Note:
- If not found bieuthuc or dieukien is TRUE, the CASE statement will return the results in the ELSE clause.
- If there is no ELSE clause and no condition is TRUE, the CASE statement will return NULL.
- Conditions are evaluated in the order listed. When a condition is determined to be true, the CASE statement returns the result and no longer evaluates subsequent conditions.
- CASE can be used in the following versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.
For example
Take a look and explore some examples of CASE statements in SQL Server.
Example 1: Simple CASE
Suppose you need to check the category of website Quantrimang.com, based on the Code column of the conversion table:
- If Code = '01' is 'Laptrinh-Quantrimang.com'.
- If Code = '02' is 'Congnghe-Quantrimang.com'.
- If Code = '03' is 'Cuocsong-Quantrimang.com'.
- On the contrary, that is, if it does not belong to 1 to 3, it will be in the section 'Hohoc-Quantrimang.com'.
SELECT tenchuyenmuc, Code
(CASE code
WHEN 01 THEN 'Laptrinh-Quantrimang.com'
WHEN 02 THEN 'Congnghe-Quantrimang.com'
WHEN 03 THEN 'Cuocsong-Quantrimang.com'
ELSE 'Khoahoc-Quantrimang.com'
END) AS Chuyenmuc
FROM chuyenmuc
ORDER BY Code
Returned results:
tenchuyenmuc | Code | Chuyenmuc |
SQL Server | 01 | Laptrinh-Quantrimang.com |
Linux | 02 | Congnghe-Quantrimang.com |
Python | 01 | Laptrinh-Quantrimang.com |
JavaScript | 01 | Laptrinh-Quantrimang.com |
Android | 02 | Congnghe-Quantrimang.com |
Giai tri | 03 | Cuocsong-Quantrimang.com |
Ky nang | 03 | Cuocsong-Quantrimang.com |
Science Listening | 04 | Khoahoc-Quantrimang.com |
Scientific research | 05 | Khoahoc-Quantrimang.com |
Example 2: Searched CASE
SELECT tenchuyenmuc,
CASE
WHEN code = 01 THEN 'Laptrinh-Quantrimang.com'
WHEN code = 02 THEN 'Congnghe-Quantrimang.com'
WHEN code = 03 THEN 'Cuocsong-Quantrimang.com'
ELSE 'Khoahoc-Quantrimang.com'
END
FROM chuyenmuc;
You can also not use the ELSE condition like this:
SELECT tenchuyenmuc, Code
(CASE code
WHEN 01 THEN 'Laptrinh-Quantrimang.com'
WHEN 02 THEN 'Congnghe-Quantrimang.com'
WHEN 03 THEN 'Cuocsong-Quantrimang.com'
END) AS Chuyenmuc
FROM chuyenmuc
ORDER BY Code
Or:
SELECT tenchuyenmuc,
CASE
WHEN code = 01 THEN 'Laptrinh-Quantrimang.com'
WHEN code = 02 THEN 'Congnghe-Quantrimang.com'
WHEN code = 03 THEN 'Cuocsong-Quantrimang.com'
END
FROM chuyenmuc;
When the ELSE clause is omitted, if neither condition is true, the CASE statement returns NULL.
Example 3: Compare 2 conditions
Here is an example that illustrates how to use the CASE statement to compare different conditions:
SELECT
CASE
WHEN code
Remember that the conditions are compared one at a time in the order they are listed. When a condition is determined to be true, the CASE statement returns the result immediately and does not evaluate the other conditions. So you need to be careful when choosing the order in which to list your conditions.
UPDATE command with CASE command
SQL Server also allows programmers to use the CASE command with the UPDATE command. Suppose you want to update a student's age based on CASE command conditions. You can do this using the following condition:
- If student age is 22, update to 33.
- If student age is 17, update to 18.
Let's analyze this example in more detail in the STUDENT table. You can fully satisfy the condition by using the following update command with 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
Result:
Return type
The CASE expression returns results that depend on the context in which it is used. For example:
- If it is used in a string context, the result will be a string.
- If it is used in a numeric context, the results will be integer, float, and decimal values.
Difference between Simpe Case and Searched CASE
Simple CASE |
Searched CASE |
There is an expression between the CASE keyword and the WHEN clause. For example, CASE. |
WHEN Condition 1 THEN Command 1. There is no expression between the word CASE and the WHEN clause. For example, |
This statement is used for simple equality testing and determining results by comparing an expression with a set of multiple values. |
This command checks the condition for each “when” command. It helps to solve more complex conditions than simple CASE. |
The CASE command simply supports equality testing. |
The searched CASE command supports any process that returns a Boolean value using Boolean_Expression. It includes equal or zero operators. |
Limitations of CASE statement in SQL Server
- The CASE statement does not allow us to control the execution flow of procedures and functions in SQL Server.
- The CASE command can include several conditions, but it only operates in a sequential model. When one of the conditions is true, it stops checking further statements.
- The CASE statement does not allow NULL values to be included in a table.
See more specific examples of the CASE function in SQL Server
Previous article: Check version information in SQL Server
Next article: COALESCE command in SQL Server