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:
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:
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
CASEexpression.zip
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.
Code Block
CASE-Simple.sqlce
Simple form.SELECT Title, Director, ReleaseDate,
CASE Title
WHEN 'Limitless' THEN 'Mystery/Thriller'
WHEN 'Source Code' THEN 'Action/Mystery/Romance'
WHEN 'Insidious' THEN 'Fantasy/Horror/Thriller'
WHEN 'Sucker Punch' THEN 'Action/Adventure/Fantasy'
WHEN 'The Lincoln Lawyer' THEN 'Crime/Drama'
WHEN 'Rango' THEN 'Animation/Adventure/Comedy'
ELSE 'Unknown Movie/Genre'
END AS Genre
FROM Movies;
Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
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.
Code Block
CASE-Search.sqlce
Searched form.SELECT EmpId, FirstName, LastName, Salary,
CASE
WHEN Salary < 10000.00 THEN 'Less than 10000'
WHEN Salary BETWEEN 10000.00 AND 20000.00 THEN 'Between 10000 and 20000'
WHEN Salary BETWEEN 20000.00 AND 30000.00 THEN 'Between 20000 and 30000'
WHEN Salary BETWEEN 30000.00 AND 40000.00 THEN 'Between 30000 and 40000'
WHEN Salary BETWEEN 40000.00 AND 50000.00 THEN 'Between 40000 and 50000'
ELSE 'More than 50000'
END AS SalaryStatus
FROM Employees;
We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting,was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.
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.
CASEexpression.zip
