When working with the database, we may sometimes require to fetch data from the database based on certain conditions, in that case, we can use SQL server CASE statement, often referred as Switch case statements.
We can also use cursor/loop depending on our requirements but CASE statements are the best alternative to cursor/loop. It evaluates a list of conditions and returns one of the multiple possible result expressions.
You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used within SELECT statement, WHERE clauses, Order by clause, HAVING clauses, & in statements such as SELECT, UPDATE, DELETE and SET.
Syntax for SQL Case statement
Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
input_expression is the expression evaluated when the simple CASE format is used while
Boolean_expression Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
CASE expression has two formats:
- The simple CASE expression compares an expression to a set of simple expressions to determine the result.
CASE input_expression WHEN expression_1 THEN Result_1 WHEN expression_2 THEN Result_2 ELSE Result END?
- The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
DECLARE @TestingCaseExpression INT SET @TestingVal = 3 SELECT CASE @TestingVal WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' ELSE 'Other' END?
- The searched CASE expression evaluates a set of Boolean expressions to determine the result.
CASE WHEN Boolean_expression_1 THEN Result_1 WHEN Boolean_expression_2 THEN Result_2 ELSE Result END?
- It evaluates, in the order specified, Boolean_expression for each WHEN clause & Returns result_N of the first Boolean_expression that evaluates to TRUE.
DECLARE @TestingValue INT SET @TestingValue = 5 SELECT CASE WHEN @TestingValue <=3 THEN 'Top 5' ELSE 'Other' END?
Examples of SQL server switch case statement
Now let's get started with some examples in SQL server switch case using AdventureWorks2012 test database, if you don't have this database you can download it from here https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks after downloading .bak file of the database from this URL Restore (Import) database from .bak file in SQL server if you have SQL server, else you can download SQL server various version.
Select statement with a simple Case example
USE AdventureWorks2012; GO SELECT ProductNumber, Category = CASE ProductLine WHEN 'M' THEN 'Mountain' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, Name FROM Production.Product ORDER BY ProductNumber; GO
the above script is a simple
CASE expression allows for only an equality check; no other comparisons are made. The following example uses the
CASE expression to change the display of product line categories to make them more understandable.
Here is the output of the above script when executed on my local SQL server
Select statement with searched Case
Let's try to use a searched case statement in which we will use the boolean expression, in the below example we will get the list price as a text comment based on the price range for a product.
USE AdventureWorks2012; GO SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber ; GO
Case statement with Order By clause
Let's check a simple example of ORDER By clause with case statement in which we will Order data based on conditions, so in the below example script If DeptIp=4 then we are running order by as Descending of FirstName else if DeptId= 3, we are ordering data by Ascending Order of LastName
SELECT * FROM dbo.MyEmployees ORDER BY CASE DeptID WHEN '4' THEN FirstName END Desc, CASE DeptID WHEN '3' THEN LastName END ASC
Using CASE in an UPDATE statement
USE AdventureWorks2012; GO UPDATE HumanResources.Employee SET VacationHours = ( CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0;
In the above script, we are using the CASE expression in an UPDATE statement to determine the value that is set for the column
VacationHours for employees with
SalariedFlag set to 0. When subtracting 10 hours from
VacationHours results in a negative value,
VacationHours is increased by 40 hours; otherwise,
VacationHours is increased by 20 hours.
Having Clause with CASE expression
USE AdventureWorks2012; GO SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING (MAX(CASE WHEN Gender = 'M' THEN ph1.Rate ELSE NULL END) > 80.00 OR MAX(CASE WHEN Gender = 'F' THEN ph1.Rate ELSE NULL END) > 60.00) ORDER BY MaximumRate DESC;
The above SQL query returns the maximum hourly rate for each job title in the
HumanResources.Employee table. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 80 dollars or women with a maximum pay rate greater than 60 dollars.
You may also like:
If you need more Case statement example you can find it here & if you have any questions related to above article, feel free to ask it below in comment's section.