SQL Server CASE Statement – Simple Explanation

SQL server CASE statement is very useful or powerful when it comes to the execution of conditional logic in SQL statements. It allows returning various values depending on particular conditions, similarly to a programmatic CASE IF-THEN-ELSE functionality. This feature is priceless in the modification of the output inside the query results set and expands data transformation, probably even classification and reporting performance. In this paper a straightforward description of the CASE statement and an enumeration of examples is given.

Types of CASE Statements

SQL Server CASE There are two main forms of CASE statements in SQL Server:

• Simple CASE : This is the simplest of the types, whereby one column or expression is compared with a list of values.
• Searched CASE : This is a type that examines several Boolean conditions as well as gives outcomes founded on the initial one of these conditions where it returns to the result of set to TRUE.

Simple CASE Statement

The Simple CASE statement compares an expression to a set of simple expressions to determine the result. The syntax is as follows.

The expression is compared against each of the values of the WHEN clauses and it gets evaluated. A match, in case it is found, returns the respective result. In the ELSE clause, resultN is returned in case nothing was matched. In case the ELSE clause is not present and no match is made, the CASE statement yields NULL.

Example1:

Consider a table named Users with columns Name and StatusCode. The following query uses a Simple CASE statement to display the status of each user based on their StatusCode:

In this example, if the StatusCode is 1, the Status column will display 'Active'. If the StatusCode is 0, the Status column will display 'Inactive'. For any other StatusCode value, the Status column will display 'Unknown'.

Searched CASE Statement

The Searched CASE statement evaluates a list of boolean expressions to determine the result. The syntax is as follows:

1. CASE  
2.  WHEN condition1 THEN result1  
3.  WHEN condition2 THEN result2  
4.  ...  
5.  ELSE resultN  
6.  END  

All the conditions are checked sequentially. Where a condition returns CASE TRUE, a result is returned. In case no condition is tested to TRUE, then the resultN of the ELSE clause is returned. In the event that ELSE clause is not present and no condition in the statement equals to TRUE, CASE statement outputs NULL values.

Example2:
Consider a table named Students with columns Name and Marks. The following query uses a Searched CASE statement to assign a grade to each student based on their Marks.

1. SELECT Name, Marks,  
2. CASE  
3. WHEN Marks >= 90 THEN 'Grade A'  
4. WHEN Marks >= 75 THEN 'Grade B'  
5. ELSE 'Grade C'  
6. END AS Grade  
7. FROM Students;  

In this example, if the Marks are greater than or equal to 90, the Grade column will display 'Grade A'. If the Marks are greater than or equal to 75 but less than 90, the Grade column will display 'Grade B'. For any other Marks value (less than 75), the Grade column will display 'Grade C'.

Benefits of Using Case Statements

• Conditional Logic in a Query: CASE statements can enable you to incorporate conditional logic into your SQL query, with no need to write special procedural code or programs.
• Data Transformation: They can be used to transform the values of data according to the desired criteria and you could standardize or categorize data accordingly as you desire.
• Customized Reporting: CASE statements also enable you to generate specialized reports in the form of customization of the output according to the conditions of the data.
• Readability: It allows you to clean up conditional logic in query and the structural integrity of your SQL code can thus be more easily maintained with CASE statements.

  

Conclusion

Use of CASE statement is an important and strongly versatile property in SQL server in implementing conditional logic and formatting in queries. You may be required to transform data or classify records, or create custom reports, and CASE statement will help you provide a powerful and yet flexible solution. When you know how to utilize CASE statements, you will increase the strength and comprehensibility of your SQL statements and you will be able to make them dynamic and flexible to use in various data manipulation cases.