Understanding SQL server switch case (With Example)

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.

Where, 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.

Syntax:

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.

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.

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

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.

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.