Microsoft® SQL Server® 2012 Bible

182

Part II: Building Databases and Working with Data

Best Practice

When programmers write procedural code, it’s often because part of the formula changes depending

on the data. To a procedural mind-set, the best way to handle this is to loop through the rows and use

multiple IF statements to branch to the correct formula. However, using a CASE expression to handle

the various calculations and executing the entire operation in a single query enable SQL Server to

optimize the process and make it dramatically faster.

Because the case expression returns an expression, it may be used anywhere in the SQL

DML statement (SELECT, INSERT, UPDATE, DELETE) where an expression may be used,

including column expressions, join conditions, WHERE conditions, having conditions, in the

ORDER BY, or even embedded in a longer expression. A case expression can even be used

mid-expression to create a dynamic formula — very powerful.

The CASE statement has two forms, simple and searched, described in the following sections.

Simple Case

With the simple CASE, the variable is presented fi rst, and then each test condition is listed.

However, this version of CASE is limited in that it can perform only equal comparisons. The

CASE expression sequentially checks the WHEN conditions and returns the THEN value of

the fi rst true WHEN condition.

In the following example, based on the AdventureWorks database, the SalariedFlag in the

Employee table is set to true for salaried employees and false for those employees that are

not salaried. The CASE expression compares the value in the SalariedFlag column with each

possible bit setting and returns the character string 'Exempt' or 'Non-Exempt' based on

the bit setting:

USE AdventureWorks

GO

SELECT

FirstName+' '+LastName EmployeeName,

CASE SalariedFlag

WHEN 1 THEN 'Exempt'

WHEN 0 THEN 'Non-Exempt'

END SalaryType

FROM HumanResources.Employee e

INNER JOIN Person.Person p

ON e.BusinessEntityID = p.BusinessEntityID

Result:

EmployeeName SalaryType

Ken Sánchez Exempt

Terri Duffy Exempt

c08.indd 182c08.indd 182 7/30/2012 4:21:13 PM7/30/2012 4:21:13 PM

http://www.it-ebooks.info