CASE Statement/Expression Examples and Explanation

ASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

Two basic formulations for CASE expression


1) Simple CASE expressions

A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A 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.

Syntax:

CASE expression

WHEN expression1 THEN expression1

[[WHEN expression2 THEN expression2] [...]]

[ELSE expressionN]


END

Example:

DECLARE @TestVal INT

SET @TestVal = 3

SELECT

CASE @TestVal


WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 3 THEN 'Third'

ELSE 'Other'


END



2) Searched CASE expressions

A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:

Syntax:

CASE

WHEN Boolean_expression1 THEN expression1


[[WHEN Boolean_expression2 THEN expression2] [...]]

[ELSE expressionN]

END

Example:

DECLARE @TestVal INT

SET @TestVal = 5


SELECT

CASE

WHEN @TestVal <=3 THEN 'Top 3'

ELSE 'Other'

END

CASE Statement in ORDER BY Clause - ORDER BY using Variable

Stored Procedure takes variable OrderBy as input parameter.

SP uses EXEC (or sp_executesql) to execute dynamically build SQL.

This was taking big hit on performance. The issue was how to improve the performance as well as remove the logic of preparing OrderBy from application. The solution I came up was using multiple CASE statement. This solution is listed here in simple version using AdventureWorks sample database. Another challenge was to order by direction of ascending or descending direction. The solution of that issue is also displayed in following example. Test the example with different options for @OrderBy and @OrderByDirection.

Currently:

Database only solution:

USE AdventureWorks


GO

DECLARE @OrderBy VARCHAR(10)

DECLARE @OrderByDirection VARCHAR(1)

SET @OrderBy = 'State' ----Other options Postal for PostalCode,


---- State for StateProvinceID, City for City

SET @OrderByDirection = 'D' ----Other options A for ascending,

---- D for descending

SELECT AddressID, City, StateProvinceID, PostalCode


FROM person.address

WHERE AddressID < 100

ORDER BY

CASE WHEN @OrderBy = 'Postal'

AND @OrderByDirection = 'D'


THEN PostalCode END DESC,

CASE WHEN @OrderBy = 'Postal'

AND @OrderByDirection != 'D'

THEN PostalCode END,


CASE WHEN @OrderBy = 'State'

AND @OrderByDirection = 'D'

THEN StateProvinceID END DESC,

CASE WHEN @OrderBy = 'State'


AND @OrderByDirection != 'D'

THEN StateProvinceID END,

CASE WHEN @OrderBy = 'City'

AND @OrderByDirection = 'D'


THEN City END DESC,

CASE WHEN @OrderBy = 'City'

AND @OrderByDirection != 'D'

THEN City END


GO