How to Use the CASE Expression in SQL Server

In this tutorial you will learn how to use the CASE expression. A CASE expression is given a value that runs through the conditional logic and returns a value. Because CASE can hold only one value at a time, it is allowed with similar expressions such as the SELECT, WHERE, HAVING, and ORDER BY clauses.  There are two forms of CASE expressions, simple and searched, which we will show examples of.


Setting Up

For the two examples we will be working with two tables, Employees and Movies. The queries for the tables are included in the source code below, download it and execute them in Microsoft SQL Server.

Simple CASE Form

The simple form allows you to compare a value with a list of possible values and return a value back for the first match. If no value matches the tested value, the CASE expression returns the value that appears in the ELSE clause.

For example, we will use the Movie table to produce a genre category for the movies. To use a CASE expression, we will put it in the SELECT clause to produce the genre column.



This query produces the following output:

As you can see in the code snippet, we made a CASE expression to look for specific title names and give it a genre based on the title. So if the title is Limitless, then give it a genre of Mystery/Thriller and if not then continue checking each title in the expression for a match.

Searched CASE Form

The searched CASE form is more flexible because it allows you to specify logical expressions in the WHEN clauses rather than one certain value. Like the simple form, the searched CASE expression returns the value in the THEN clause that is associated with the first WHEN expression that evaluates to true, and if there are no matches then the value in the ELSE clause is returned.

In this example we use the Employees table to display the salary status of the employees. We use the BETWEEN keyword in the WHEN clause to specify a value between two numbers.



This query produces the following output:

As you can see, the SalaryStatus displays what two numbers the salary of the employee falls into. For example, since John Johnson has a salary of 32000, his salary status is ‘Between 30000 and 40000′.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *