Microsoft® SQL Server® 2012 Bible

183

Chapter 8: Data Types, Expressions, and Scalar Functions

8

Roberto Tamburello Exempt

Rob Walters Non-Exempt

The CASE expression concludes with an end and an alias. In this example, the CASE expres-

sion evaluates the SalariedFlag column but produces the SalaryType column in the SQL

SELECT result set.

Be careful if you use NULL in a simple CASE. This translates literally to “=NULL” and not to

“IS NULL”. You can get unintended results if you are not careful.

Boolean Case

The boolean form of case (called the searched case in BOL) is more fl exible than the simple

form in that each individual case has its own boolean expression. Therefore, not only can

each WHEN condition include comparisons other than =, but the comparison may also refer-

ence different columns:

SELECT

CASE

WHEN 1<0 THEN 'Reality is gone.'

WHEN CURRENT_TIMESTAMP = '20161221'

THEN 'Patrick gets his driver''s license.'

WHEN 1>0 THEN 'Life is normal.'

END AS RealityCheck;

Following is the result of the query when executed on Patrick’s sixteenth birthday:

RealityCheck

--------------------------------

Patrick gets his driver's license.

As with the simple case, the fi rst true WHEN condition halts evaluation of the case

and returns the THEN value. In this case (a pun!), if 1 is ever less than 0, then the

RealityCheck case accurately reports 'reality is gone'. (When the author’s son

turns 16, the RealityCheck will again accurately warn of his legal driving status.) If

neither of these conditions is true, and 1 is still greater than 0, then all is well with reality

and 'Life is normal'.

The point of the preceding code is that the searched CASE expression offers more fl exibility

than the simple CASE. This example mixed various conditional checks (<,=,>), and differ-

ing data was checked by the WHEN clause.

The boolean CASE expression can handle complex conditions, including boolean AND and OR

operators. The following code sample uses a batch to set up the CASE expression (including

T-SQL variables, which are explained in Chapter 16, “Programming with T-SQL”), and the

CASE includes an AND and a BETWEEN operator:

DECLARE @b INT, @q INT;

SET @b = 2007;

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

http://www.it-ebooks.info