SQL Server – Power of CASE Function

* Compatibility SQL Server 2000 onwards
CASE statement can be used to introduce conditional logic. CASE can be used to evaluate multiple statements and return one possible output.

CASE can be compared to Switch statement in modern programming languages like C#.

CASE statement provides following benefits:

• Ability to format output
• Ability to perform conditional logic on a Column
• Ability to optimize query by emitting the need to write multiple queries to
  return a result set
• Ability to perform conditional Update
• Ability to provide dynamic Order By in single query

CASE functions are of 2 types:

• Simple [Comparison is done with substituted inline values]
• Searched [Comparison is done on Column level]

For more details on CASE syntax visit: MSDN

Let us see CASE statement in action for the different type of scenarios:

(a) Formatted output using Simple CASE function:

SELECT
OrderID, OrderDate, ShipVia,
Shipper =
(CASE SHIPVIA
WHEN 1 THEN 'Speedy Express'
WHEN 2 THEN 'United Package'
WHEN 3 THEN 'Federal Shipping'
ELSE 'Unknown'
END)
FROM
Orders



(b) Column level comparison using Searched CASE function:

SELECT
OrderID, OrderDate, Freight,
FreightCategory =
(CASE
WHEN Freight <= 0 THEN 'No Freight'

WHEN (Freight >= 1 AND Freight <= 100) THEN 'Low Freight'

WHEN (Freight >= 100 ) THEN 'High Freight'

ELSE 'Unknown Freight'

END)
FROM
Orders

(c) Dynamic Order By functionality using CASE function:

This is helpful in the scenario when query needs to return the output by multiple columns. CASE function can be used to accomplish this by using Column names as variable which can be evaluated and substituted in the order by clause criteria.

Sample example below shows a query which can be dynamically sorted by LastName, FirstName and EmployeeID (default), the same query can be packaged into a stored procedure where it can take column names as a parameter and return the output accordingly:

--dynamic Order By
Declare @OrderBy Varchar(100)
--order by LastName
SELECT @OrderBy = 'LastName'

SELECT
EmployeeID, LastName, FirstName, Title, BirthDate, HireDate
FROM
Employees
ORDER BY
CASE
WHEN @OrderBy = 'LastName' THEN LastName
WHEN @OrderBy = 'FirstName' THEN FirstName
--default
ELSE Cast(EmployeeID As Varchar)
END

--dynamic Order By
Declare @OrderBy Varchar(100)
--order by FirstName
SELECT @OrderBy = 'FirstName'

SELECT
EmployeeID, LastName, FirstName, Title, BirthDate, HireDate
FROM
Employees
ORDER BY
CASE
WHEN @OrderBy = 'LastName' THEN LastName
WHEN @OrderBy = 'FirstName' THEN FirstName
--default
ELSE Cast(EmployeeID As Varchar)
END



(d) Conditional Update functionality using CASE function:

This is helpful in the scenario when multiple updates are needed for a particular table. CASE function can be used to accomplish the same operation using single update statement instead of writing multiple update statements for each criteria.

Sample example below adds $8 to all the Products which have UnitPrice less than 50, adds $5 to Products having UnitPrice greater than 50 and less than 100, adds $3 to Products having UnitPrice greater than 100 and finally for conditions which are falling in any of the criteria it puts the same UnitPrice (unchanged):

--put in temporary transaction to perform rollback
BEGIN TRAN
UPDATE Products SET UnitPrice =
CASE
WHEN (UnitPrice >= 0 AND UnitPrice <= 50) THEN (UnitPrice + 8)

WHEN (UnitPrice >= 50 AND UnitPrice <= 100) THEN (UnitPrice + 5)

WHEN (UnitPrice >= 100) THEN (UnitPrice + 3)
ELSE UnitPrice
END
--roll back the changes
ROLLBACK