Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 55

10255       68          2490.50  Between 1000 and 3000 

10256       88          517.80   Less than 1000 

10257       35          1119.90  Between 1000 and 3000 

... 

 

(830 row(s) affected)

You can see that every simple CASE expression can be converted to the searched CASE form, but 

the reverse is not necessarily true.

T-SQL supports some functions that you can consider as abbreviations of the CASE expression: 

ISNULL, COALESCE, IIF, and CHOOSE. Note that of the four, only COALESCE is standard. Also, IIF and 

CHOOSE are available only in SQL Server 2012.

The ISNULL function accepts two arguments as input and returns the first that is not NULL, or NULL 

if both are NULL. For example ISNULL(col1, ‘’) returns the col1 value if it isn’t NULL, and an empty string 

if it is NULL. The COALESCE function is similar, only it supports two or more arguments and returns 

the first that isn’t NULL, or NULL if all are NULL. As mentioned earlier, when there’s a choice, it is 

generally recommended that you use standard features, hence it is recommended that you use the 

COALESCE function and not ISNULL.

The nonstandard IIF and CHOOSE functions were added in SQL Server 2012 to support easier 

migrations from Microsoft Access. The function IIF(, , ) returns 

expr1 if logical_expression is TRUE and expr2 otherwise. For example, the expression IIF(col2 <> 0, 

col2/col1, NULL) returns the result of col2/col1 if col1 is not zero, otherwise it returns a NULL. The 

function CHOOSE(, , , …, ) returns the expression from the list in the 

specified index. For example, the expression CHOOSE(3, col1, col2, col3) returns the value of col3. Of 

course, actual expressions that use the CHOOSE function tend to be more dynamic—for example, 

relying on user input.

So far, I’ve just used a few examples to familiarize you with the CASE expression and functions that 

can be considered abbreviations of the CASE expression. Even though it might not be apparent at this 

point from these examples, the CASE expression is an extremely powerful and useful language element.