CASE statement in SQL Server

This article will show you in detail how to use the CASE statement handling function in SQL Server with specific syntax and examples to better visualize and capture functions.

Describe

CASE statement used to set branch conditions in SQL Server, similar to the function of IF-THEN-ELSE statement.

CASE has 2 formats:

1. Simple CASE function, also called Simple CASE.
2. The search function CASE is also called Searched CASE.

Inside:

1. Simple CASE is to compare an expression with a set of simple expressions to determine the result.
2. Searched CASE is to evaluate a set of Boolean expressions to determine results.
3. Both formats support the ELSE argument (but not required).

Syntax

To use CASE statements 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 

Or 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 

Parameters :

1. bieuthuc_dauvao: The expression will be compared to each value provided.
2. bieuthuc_1, bieuthuc_2, bieuthuc_n: expressions will be used to compare in turn with the input expression. When an expression matches bieuthu_dauvao, CASE will execute the next statement and no further comparison.
3. dieukien_1, dieukien_2, dieukien_n : conditions reviewed, approved in the order listed. When a condition is determined to be true, CASE returns the result and no further conditions are evaluated. All conditions must be the same data type.
4. ketqua_1, ketqua_2, ketqua_n : the result returned after considering the condition is true. All values ​​must be the same data type.

Note :

1. If no match is found for TRUE, the CASE statement will return the result in the ELSE clause.
2. If there is no ELSE clause and there is no TRUE condition then the CASE statement will return NULL.
3. Conditions are evaluated in the order listed. When a condition is determined to be true, the CASE statement returns the result and no further conditions are evaluated.
4. CASE can be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and 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 section of the TipsMake.com website, based on the Code column of the conversion table:

1. If Code = '01' is 'Laptrinh-TipsMake.com'.
2. If Code = '02' is 'Congnghe-TipsMake.com'.
3. If Code = '03' is 'Cuocsong-TipsMake.com'.
4. On the contrary, that is not from 1 to 3 then in the section 'Khoahoc-TipsMake.com'.

 SELECT tenchuyenmuc, Code 
  (CASE code 
  WHEN 01 THEN 'Laptrinh-TipsMake.com' 
  WHEN 02 THEN 'Congnghe-TipsMake.com' 
  WHEN 03 THEN 'Cuocsong-TipsMake.com' 
  ELSE 'Khoahoc-TipsMake.com' 
  END) AS Chuyenmuc 
 FROM chuyenmuc 
 ORDER BY Code 

Results returned:

Example 2: Searched CASE

 SELECT tenchuyenmuc, 
  CASE 
  WHEN code = 01 THEN 'Laptrinh-TipsMake.com' 
  WHEN code = 02 THEN 'Congnghe-TipsMake.com' 
  WHEN code = 03 THEN 'Cuocsong-TipsMake.com' 
  ELSE 'Khoahoc-TipsMake.com' 
  END 
 FROM chuyenmuc; 

You may also not use ELSE conditions like this:

 SELECT tenchuyenmuc, Code 
  (CASE code 
  WHEN 01 THEN 'Laptrinh-TipsMake.com' 
  WHEN 02 THEN 'Congnghe-TipsMake.com' 
  WHEN 03 THEN 'Cuocsong-TipsMake.com' 
  END) AS Chuyenmuc 
 FROM chuyenmuc 
 ORDER BY Code 

Or:

 SELECT tenchuyenmuc, 
  CASE 
  WHEN code = 01 THEN 'Laptrinh-TipsMake.com' 
  WHEN code = 02 THEN 'Congnghe-TipsMake.com' 
  WHEN code = 03 THEN 'Cuocsong-TipsMake.com' 
  END 
 FROM chuyenmuc; 

When the ELSE clause is omitted, if no condition is true, the CASE statement returns NULL.

Example 3: Comparing two conditions

Here is an example that illustrates how to use CASE statements to compare different conditions:

 SELECT 
  CASE 
  WHEN code < 2 THEN 'Laptrinh-TipsMake.com' 
  WHEN code = 2 THEN 'Congnghe-TipsMake.com' 
  END 
 FROM chuyenmuc; 

Remember that the conditions are compared in turn in the order listed. When a condition is determined to be true, the CASE statement will return the result immediately and no other conditions will be evaluated. So you need to be careful when choosing the order that lists your conditions.

See more specific examples of CASE functions in SQL Server

Previous article: Check version information in SQL Server

Next lesson: COALESCE command in SQL Server