T-SQL has always included support for procedural programming in the form of control-of-flow statements and cursors. One thing that throws developers from other languages off their guard when migrating to SQL is the peculiar three-valued logic (3VL) we enjoy. In Chapter 1 we introduced you to SQL 3VL and we will expand on this topic further in this chapter. SQL 3VL is different from most other programming languages’ simple two-valued Boolean logic. We will also discuss T-SQL control-of-flow constructs, which allow you to change the normally sequential order of statement execution. Control-of-flow statements allow you to branch your code logic with statements like IF. . .ELSE. . ., perform loops with statements like WHILE, and perform unconditional jumps with the GOTO statement. We will also introduce CASE expressions and CASE-derived functions that return values based on given comparison criteria in an expression. Finally, we will finish the chapter by explaining a topic closely tied to procedural code: SQL cursors.

Three-Valued Logic

SQL Server 2012, like all ANSI-compatible SQL DBMS products, implements a peculiar form of logic known as 3VL. 3VL is necessary because SQL introduces the concept of NULL to serve as a placeholder for values that are not known at the time they are stored in the database. The concept of NULL introduces an unknown logical result into SQL’s ternary logic system. We will introduce SQL 3VL with a simple set of propositions:

• Consider the proposition “1 is less than 3.” The result is logically true because the value of the number 1 is less than the value of the number 3.
• The proposition “5 is equal to 6” is logically false because the value of the number 5 is not equal to the value of the number 6.
• The proposition “X is greater than 10” presents a bit of a problem. The variable X is an algebraic placeholder for an actual value. Unfortunately, we haven’t told you what value X stands for at this time. Because you don’t know what the value of X is, you can’t say the statement is true or false; instead you can say the result is unknown. SQL NULL represents an unknown value in the database in much the same way that the variable X represents an unknown value in this proposition, and comparisons with NULL produce the same unknown logical result in SQL.

Because NULL represents unknown values in the database, comparing anything with NULL (even other NULLs) produces an unknown logical result. Figure 3-1 is a quick reference for SQL Server 3VL, where p and q represent 3VL result values.

As mentioned previously, the unknown logic values shown in the chart are the result of comparisons with NULL. The following predicates, for example, all evaluate to an unknown result:

@x  =  NULL
FirstName <> NULL
PhoneNumber  >  NULL

If you used one of these as the predicate in a WHERE clause of a SELECT statement, the statement would return no rows at all—SELECT with a WHERE clause returns only rows where the WHERE clause predicate evaluates to true; it discards rows for which the WHERE clause is false or unknown. Similarly, the INSERT, UPDATE, and DELETE statements with a WHERE clause only affect rows for which the WHERE clause evaluates to true.

SQL Server provides a proprietary mechanism, the SET ANSI_NULLS OFF option, to allow direct equality comparisons with NULL using the = and <> operators. The only ISO-compliant way to test for NULL is with the IS NULL and IS NOT NULL comparison predicates. We highly recommend that you stick with the ISO-compliant IS NULL and IS NOT NULL predicates for a few reasons:

• Many SQL Server features like computed columns, indexed views, and XML indexes require SET ANSI_NULLS ON at creation time.
• Mixing and matching SET ANSI_NULLS settings within your database can confuse other developers who have to maintain your code. Using ISO-compliant NULL-handling consistently eliminates confusion.
• SET ANSI_NULLS OFF allows direct equality comparisons with NULL, returning true if you compare a column or variable to NULL. It does not return true if you compare NULLs contained in two columns, though, which can be confusing.
• To top it all off, Microsoft has deprecated the SET ANSI_NULLS OFF setting. It will be removed in a future version of SQL Server, so it’s a good idea to start future-proofing your code now.

The BEGIN and END Keywords

T-SQL uses the keywords BEGIN and END to group multiple statements together in a statement block. The BEGIN and END keywords don’t alter execution order of the statements they contain, nor do they define an atomic transaction, limit scope, or perform any function other than defining a simple grouping of T-SQL statements.

Unlike other languages, such as C++ or C#, which use braces ({ }) to group statements in logical blocks, T-SQL’s BEGIN and END keywords do not define or limit scope. The following sample C# code, for instance, will not even compile:

{
int j  =  10; } Console.WriteLine (j);

C# programmers will automatically recognize that the variable j in the previous code is defined inside braces, limiting its scope and making it accessible only inside the braces. T-SQL’s roughly equivalent code, however, does not limit scope in this manner:

BEGIN
    DECLARE @j int  =  10;
END
PRINT @j;

The previous T-SQL code executes with no problem, as long as the DECLARE statement is encountered before the variable is referenced in the PRINT statement. The scope of variables in T-SQL is defined in terms of command batches and database object definitions (such as SPs, UDFs, and triggers). Declaring two or more variables with the same name in one batch or SP will result in errors.

BEGIN. . .END is useful for creating statement blocks where you want to execute multiple statements based on the results of other control-of-flow statements like IF. . .ELSE and WHILE. BEGIN. . .END can also have another added benefit if you’re using SSMS 2012 or a good third-party SQL editor like ApexSQL Edit (www.apexsql.com/). In advanced editors like these, BEGIN. . .END can alert the GUI that a section of code is collapsible, as shown in Figure 3-2. This can speed up development and ease debugging, especially if you’re writing complex T-SQL scripts.

The IF . . . ELSE Statement

Like many procedural languages, T-SQL implements conditional execution of code using the simplest of procedural statements: the IF. . .ELSE construct. The IF statement is followed by a logical predicate. If the predicate evaluates to true, the single SQL statement or statement blocked wrapped in BEGIN. . .END is executed. If the predicate evaluates to either false or unknown, SQL Server falls through to the ELSE statement and executes the single statement or statement block following ELSE.

The example in Listing 3-1 performs up to three comparisons to determine whether a variable is equal to a specified value. The second ELSE statement executes if and only if the tests for both true and false conditions fail.

Listing 3-1.  Simple IF . . . ELSE Example

DECLARE @i int  =  NULL;
IF @i  =  10
    PRINT 'TRUE.';
ELSE IF NOT (@i  =  10)
    PRINT 'FALSE.';
ELSE
    PRINT 'UNKNOWN.';

Because the variable @i is NULL in the example, SQL Server reports that the result is unknown. If you assign the value 10 to the variable @i, SQL Server will report that the result is true; all other values will report false.

To create a statement block containing multiple T-SQL statements after either the IF statement or the ELSE statement, simply wrap your statements with the T-SQL BEGIN and END keywords discussed in the previous section. The simple example in Listing 3-2 is an IF. . .ELSE statement with statement blocks. The example uses IF. . .ELSE to check the value of the variable @direction. If @direction is ASCENDING, a message is printed, and the top ten names, in order of last name, are selected from the Person.Contact table. If @direction is DESCENDING, a different message is printed, and the bottom ten names are selected from the Person.Contact table. Any other value results in a message that @direction was not recognized. The results of Listing 3-2 are shown in Figure 3-3.

Listing 3-2.  IF . . . ELSE with Statement Blocks

DECLARE @direction NVARCHAR(20)  =  N'DESCENDING';
  
IF @direction  =  N'ASCENDING'
BEGIN
        PRINT 'Start at the top!';
  
        SELECT TOP (10)
        LastName,
        FirstName,
        MiddleName
        FROM Person.Person
        ORDER BY LastName ASC;
END
ELSE  IF @direction  =  N'DESCENDING'
BEGIN
        PRINT 'Start at the bottom!';
  
        SELECT TOP (10)
        LastName,
        FirstName,
        MiddleName
        FROM Person.Person
        ORDER BY LastName DESC;
END
ELSE

        PRINT '@direction  was not recognized!';

The WHILE, BREAK, and CONTINUE Statements

Looping is a standard feature of procedural languages, and T-SQL provides looping support through the WHILE statement and its associated BREAK and CONTINUE statements. The WHILE loop is immediately followed by a predicate, and WHILE will execute a given SQL statement or statement block bounded by the BEGIN and END keywords as long as the associated predicate evaluates to true. If the predicate evaluates to false or unknown, the code in the WHILE loop will not execute and control will pass to the next statement after the WHILE loop. The WHILE loop in Listing 3-3 is a very simple example that counts from 1 to 10. The result is shown in Figure 3-4.

Listing 3-3.  WHILE Statement Example

DECLARE @i int  =  1;
WHILE @i <  = 10
BEGIN
   PRINT @i;
   SET @i  =  @i  +  1;

END

T-SQL also includes two additional keywords that can be used with the WHILE statement: BREAK and CONTINUE. The CONTINUE keyword forces the WHILE loop to immediately jump to the start of the code block, as in the modified example in Listing 3-4.

Listing 3-4.  WHILE . . . CONTINUE Example

DECLARE @i int  =  1;
WHILE @i <  = 10
BEGIN
   PRINT @i;
   SET @i  =  @i  +  1;
  
CONTINUE; -- Force the WHILE loop to restart
  
PRINT 'The CONTINUE keyword ensures that this will never be printed.';
  
END

The BREAK keyword, on the other hand, forces the WHILE loop to terminate immediately. In Listing 3-5, BREAK forces the WHILE loop to exit during the first iteration so that the numbers 2 through 10 are never printed.

Listing 3-5.  WHILE . . . BREAK Example

DECLARE @i int  =  1;
WHILE @i <  = 10
BEGIN
   PRINT @i;
   SET @i  =  @i  +  1;
  
   BREAK; -- Force the WHILE loop to terminate
  
   PRINT 'The BREAK keyword ensures that this will never be printed.';
END

The GOTO Statement

Despite Edsger W. Dijkstra’s best efforts at warning developers (see Dijkstra’s 1968 letter, “Go To Statement Considered Harmful”), T-SQL still has a GOTO statement. The GOTO statement transfers control of your program to a specified label unconditionally. Labels are defined by placing the label identifier on a line followed by a colon (:), as shown in Listing 3-6. This simple example executes its step 1 and uses GOTO to dive straight into step 3, skipping step 2. The results are shown in Figure 3-5.

Listing 3-6.  Simple GOTO Example

PRINT 'Step 1 Begin.';
GOTO Step3_Label;
  
PRINT 'Step 2 will not be printed.';
  
Step3_Label:

PRINT 'Step 3 End.';

The GOTO statement is best avoided, since it can quickly degenerate your programs into unstructured spaghetti code. When you have to write procedural code, you’re much better off using structured programming constructs like IF. . .ELSE and WHILE statements.

The WAITFOR Statement

The WAITFOR statement suspends execution of a transaction, SP, or T-SQL command batch until a specified time is reached, a time interval has elapsed, or a message is received from Service Broker.

The WAITFOR statement has a DELAY option that tells SQL Server to suspend code execution until one of the following criteria is met or a specified time interval has elapsed. The time interval is specified as a valid time string in the format hh:mm:ss. The time interval cannot contain a date portion; it must only include the time, and it can be up to 24 hours. Listing 3-7 is an example of the WAITFOR statement with the DELAY option, which blocks execution of the batch for 3 seconds.

The RETURN Statement

The RETURN statement exits unconditionally from an SP or command batch. When you use RETURN, you can optionally specify an integer expression as a return value. The RETURN statement returns a given integer expression to the calling routine or batch. If you don’t specify an integer expression to return, a value of 0 is returned by default. RETURN is not normally used to return calculated results, except for UDFs, which offer more RETURN options (we will detail these in Chapter 4). For SPs and command batches, the RETURN statement is used almost exclusively to return a success indicator, failure indicator, or error code.

The Simple CASE Expression

The simple CASE expression returns a result expression based on the value of a given input expression. The simple CASE expression compares the input expression to a series of expressions following WHEN keywords. Once a match is encountered, CASE returns a corresponding result expression following the keyword THEN. If no match is found, the expression following the keyword ELSE is returned, and NULL is returned if no ELSE keyword is supplied.

Consider the example in Listing 3-8, which uses a simple CASE expression to count all the AdventureWorks customers on the West Coast (which we arbitrarily defined as the states of California, Washington, and Oregon). The query also uses a CTE (common table expression) which we will discuss more thoroughly in Chapter 8. The results are shown in Figure 3-6.

Listing 3-8.  Counting West Coast Customers with a Simple CASE Expression

WITH  EmployeesByRegion(Region)
AS
(
    SELECT
        CASE sp.StateProvinceCode
        WHEN 'CA' THEN 'West Coast'
        WHEN 'WA' THEN 'West Coast'
        WHEN 'OR' THEN 'West Coast'
        ELSE 'Elsewhere'
        END
    FROM  HumanResources.Employee e
    INNER JOIN Person.Person p
        ON e.BusinessEntityID  =  p.BusinessEntityID
       INNER JOIN Person.BusinessEntityAddress bea
              ON bea.BusinessEntityID  =  e.BusinessEntityID
       INNER JOIN Person.Address a
           ON a.AddressID  =  bea.AddressID
       INNER JOIN Person.StateProvince sp
              ON sp.StateProvinceID  =  a.StateProvinceID
    WHERE sp.CountryRegionCode  =  'US'
)
SELECT  COUNT(Region)  AS  NumOfEmployees,  Region
FROM    EmployeesByRegion

GROUP  BY  Region;

The CASE expression in the subquery compares the StateProvinceCode value to each of the state codes following the WHEN keywords, returning the name West Coast when the StateProvinceCode is equal to CA, WA, or OR. For any other StateProvinceCode in the United States, it returns a value of Elsewhere.

SELECT CASE sp.StateProvinceCode
        WHEN 'CA' THEN 'West Coast'
        WHEN 'WA' THEN 'West Coast'
        WHEN 'OR' THEN 'West Coast'
        ELSE 'Elsewhere'
    END

The remainder of the example simply counts the number of rows returned by the query, grouped by Region.

The Searched CASE Expression

The searched CASE expression provides a mechanism for performing more complex comparisons. The searched CASE evaluates a series of predicates following WHEN keywords until it encounters one that evaluates to true. At that point, it returns the corresponding result expression following the THEN keyword. If none of the predicates evaluates to true, the result following the ELSE keyword is returned. If none of the predicates evaluates to true and ELSE is not supplied, the searched CASE expression returns NULL.

Predicates in the searched CASE expression can take advantage of any valid SQL comparison operators (e.g., <, >, =, LIKE, and IN). The simple CASE expression from Listing 3-8 can be easily expanded to cover multiple geographic regions using the searched CASE expression and the IN logical operator, as shown in Listing 3-9. This example uses a searched CASE expression to group states into West Coast, Pacific, and New England regions. The results are shown in Figure 3-7.

Listing 3-9.  Counting Employees by Region with a Searched CASE Expression

WITH  EmployeesByRegion(Region)
AS
(
    SELECT
        CASE WHEN sp.StateProvinceCode IN ('CA', 'WA', 'OR') THEN 'West Coast'
        WHEN sp.StateProvinceCode IN ('HI', 'AK') THEN 'Pacific'
        WHEN  sp.StateProvinceCode  IN  ('CT',  'MA', 'ME', 'NH', 'RI', 'VT')
        THEN 'New England'
        ELSE 'Elsewhere'
        END
    FROM  HumanResources.Employee e
    INNER JOIN Person.Person p
        ON e.BusinessEntityID  =  p.BusinessEntityID
       INNER JOIN Person.BusinessEntityAddress bea
           ON bea.BusinessEntityID  =  e.BusinessEntityID
       INNER JOIN Person.Address a
           ON a.AddressID  =  bea.AddressID
       INNER JOIN Person.StateProvince sp
           ON sp.StateProvinceID  =  a.StateProvinceID
    WHERE sp.CountryRegionCode  =  'US'
)
SELECT  COUNT(Region)  AS  NumOfCustomers,  Region
FROM    EmployeesByRegion

GROUP  BY  Region;

The searched CASE expression in the example uses the IN operator to return the geographic area that StateProvinceCode is in: California, Washington, and Oregon all return West Coast; and Connecticut, Massachusetts, Maine, New Hampshire, Rhode Island, and Vermont all return New England. If the StateProvinceCode does not fit in one of these regions, the searched CASE expression will return Elsewhere.

SELECT
    CASE WHEN sp.StateProvinceCode IN ('CA', 'WA', 'OR') THEN 'West Coast'
        WHEN sp.StateProvinceCode IN ('HI', 'AK') THEN 'Pacific'
        WHEN  sp.StateProvinceCode  IN  ('CT',  'MA', 'ME', 'NH', 'RI', 'VT')
        THEN 'New England'
        ELSE  'Elsewhere'
    END

The balance of the sample code in Listing 3-9 counts the rows returned, grouped by Region. The CASE expression, either simple or searched, can be used in SELECT, UPDATE, INSERT, MERGE, and DELETE statements.

CASE and Pivot Tables

Many times, business reporting requirements dictate that a result should be returned in pivot table format. Pivot table format simply means that the labels for columns and/or rows are generated from the data contained in rows. Microsoft Access and Excel users have long had the ability to generate pivot tables on their data, and SQL Server 2012 supports the PIVOT and UNPIVOT operators introduced in SQL Server 2005. Back in the days of SQL Server 2000 and before, however, CASE expressions were the only method of generating pivot table-type queries. And even though SQL Server 2012 provides the PIVOT and UNPIVOT operators, truly dynamic pivot tables still require using CASE expressions and dynamic SQL. The static pivot table query shown in Listing 3-10 returns a pivot table-formatted result with the total number of orders for each AdventureWorks sales region in the United States. The results are shown in Figure 3-8.

Listing 3-10.  CASE-Style Pivot Table

SELECT
    t.CountryRegionCode,
    SUM
    (
        CASE WHEN t.Name  =  'Northwest' THEN 1
        ELSE    0
        END
    )  AS       Northwest,
    SUM
    (
        CASE WHEN t.Name  =  'Northeast' THEN 1
        ELSE    0
        END
    )  AS       Northeast,
    SUM
    (
        CASE WHEN t.Name  =  'Southwest' THEN 1
        ELSE    0
        END
    )  AS       Southwest,
    SUM
    (
        CASE WHEN t.Name  =  'Southeast' THEN 1
        ELSE    0
        END
    )  AS       Southeast,
    SUM
    (
        CASE WHEN t.Name  =  'Central' THEN 1
        ELSE    0
        END
    )  AS  Central
FROM  Sales.SalesOrderHeader soh
INNER  JOIN  Sales.SalesTerritory  t
    ON  soh.TerritoryID  =  t.TerritoryID
WHERE  t.CountryRegionCode  =  'US'
GROUP  BY  t.CountryRegionCode;

This type of static pivot table can also be used with the SQL Server 2012 PIVOT operator. The sample code in Listing 3-11 uses the PIVOT operator to generate the same result as the CASE expressions in Listing 3-10.

Listing 3-11.  PIVOT Operator Pivot Table

SELECT
    CountryRegionCode,
    Northwest,
    Northeast,
    Southwest,
    Southeast,
    Central
FROM
(
    SELECT
        t.CountryRegionCode,
        t.Name
    FROM  Sales.SalesOrderHeader  soh
    INNER JOIN Sales.SalesTerritory t
        ON  soh.TerritoryID  =  t.TerritoryID
    WHERE t.CountryRegionCode  =  'US'
)  p
PIVOT
(
    COUNT (Name)
    FOR  Name
    IN
    (
        Northwest,
        Northeast,
        Southwest,
        Southeast,
        Central
    )
)  AS  pvt;

On occasion, you might need to run a pivot table-style report where you don’t know the column names in advance. This is a dynamic pivot table script that uses a temporary table and dynamic SQL to generate a pivot table, without specifying the column names in advance. Listing 3-12 demonstrates one method of generating dynamic pivot tables in T-SQL. The results are shown in Figure 3-9.

Listing 3-12.  Dynamic Pivot Table Query

-- Declare variables
DECLARE @sql nvarchar(4000);
  
DECLARE @temp_pivot table
 (
  TerritoryID int NOT NULL PRIMARY KEY,
  CountryRegion nvarchar(20) NOT NULL,
  CountryRegionCode nvarchar(3) NOT NULL
 );
  
-- Get column names from source table rows
INSERT INTO @temp_pivot
 (
  TerritoryID,
  CountryRegion,
  CountryRegionCode
 )
 SELECT
  TerritoryID,
  Name,
  CountryRegionCode
  FROM Sales.SalesTerritory
  GROUP BY
     TerritoryID,
     Name,
     CountryRegionCode;
  
-- Generate dynamic SQL query
SET @sql  =  N'SELECT' +
    SUBSTRING(
       (
         SELECT N', SUM(CASE WHEN t.TerritoryID  =  ' +
           CAST(TerritoryID AS NVARCHAR(3)) +
        N' THEN 1 ELSE 0 END) AS '  +  QUOTENAME(CountryRegion) AS "*"
        FROM @temp_pivot
        FOR XML PATH('')
    ), 2, 4000) +
    N' FROM Sales.SalesOrderHeader soh ' +
       N' INNER JOIN Sales.SalesTerritory t ' +
       N' ON soh.TerritoryID  =  t.TerritoryID; ' ;
  
-- Print and execute dynamic SQL
PRINT @sql;
  

EXEC (@sql);

The script in Listing 3-12 first declares an nvarchar variable that will hold the dynamically generated SQL script and a table variable that will hold all of the column names, which are retrieved from the row values in the source table.

-- Declare variables
DECLARE @sql nvarchar(4000);
  
DECLARE @temp_pivot table
 (
  TerritoryID int NOT NULL PRIMARY KEY,
  CountryRegion nvarchar(20) NOT NULL,
  CountryRegionCode nvarchar(3) NOT NULL
 );

Next, the script grabs a list of distinct territory-specific values from the table and stores them in the @temp_pivot table variable. These values from the table will become column names in the pivot table result.

-- Get column names from source table rows
INSERT INTO @temp_pivot
 (
  TerritoryID,
  CountryRegion,
  CountryRegionCode
 )
 SELECT
  TerritoryID,
  Name,
  CountryRegionCode
  FROM Sales.SalesTerritory
  GROUP BY
     TerritoryID,
     Name,
     CountryRegionCode;

The script then uses FOR XML PATH to efficiently generate the dynamic SQL SELECT query that contains CASE expressions and column names generated dynamically based on the values in the @temppivot table variable. This SELECT query will create the dynamic pivot table result.

-- Generate dynamic SQL query
SET @sql  =  N'SELECT' +
    SUBSTRING(
       (
         SELECT N', SUM(CASE WHEN t.TerritoryID  =  ' +
           CAST(TerritoryID AS NVARCHAR(3)) +
        N' THEN 1 ELSE 0 END) AS '  +  QUOTENAME(CountryRegion) AS "*"
        FROM @temp_pivot
        FOR XML PATH('')
    ), 2, 4000) +
    N' FROM Sales.SalesOrderHeader soh ' +
       N' INNER JOIN Sales.SalesTerritory t ' +
       N' ON soh.TerritoryID  =  t.TerritoryID; ' ;

Finally, the dynamic pivot table query is printed out and executed with the T-SQL PRINT and EXEC statements.

-- Print and execute dynamic SQL
PRINT @sql;
EXEC (@sql);

Listing 3-13 shows the dynamic SQL pivot table query generated by the code in Listing 3-12.

Listing 3-13.  Autogenerated Dynamic SQL Pivot Table Query

SELECT SUM
(
    CASE  WHEN  t.TerritoryID  =  1  THEN       1
        ELSE 0
    END
)  AS   [Northwest],
SUM
(
    CASE  WHEN  t.TerritoryID  =  2  THEN       1
        ELSE 0
    END
)  AS   [Northeast],
SUM
(
    CASE  WHEN  t.TerritoryID  =  3  THEN       1
        ELSE 0
    END
)  AS  [Central],
SUM
(
    CASE  WHEN  t.TerritoryID  =  4  THEN       1
        ELSE 0
    END
)  AS   [Southwest],
SUM
(
    CASE  WHEN  t.TerritoryID  =  5  THEN       1
        ELSE 0
    END
)  AS   [Southeast],
SUM
(
    CASE  WHEN  t.TerritoryID  =  6  THEN       1
        ELSE 0
    END
)  AS  [Canada],
SUM
(
    CASE  WHEN  t.TerritoryID  =  7  THEN       1
        ELSE 0
    END
)  AS  [France],
SUM
(
    CASE  WHEN  t.TerritoryID  =  8  THEN       1
        ELSE 0
    END
)  AS  [Germany],
SUM
(
    CASE  WHEN  t.TerritoryID  =  9  THEN       1
        ELSE 0
    END
)  AS  [Australia],
SUM
(
    CASE  WHEN  t.TerritoryID  =  10  THEN       1
        ELSE 0
    END
)  AS  [United Kingdom]
FROM  Sales.SalesOrderHeader soh
INNER  JOIN  Sales.SalesTerritory  t
    ON  soh.TerritoryID  =  t.TerritoryID;

The IIF Statement

SQL Server 2012 simplifies the standard CASE statement by introducing the concept of an IIF statement. You get the same results as you would using the CASE statement but with much less code. Those familiar with Microsoft .NET will be glad to see the same functionality is now part of T-SQL.

The syntax is simple. The command takes a Boolean expression, a value when the expression equates to true and a value when the expression equates to false. Listing 3-14 show two examples. One example uses variables and the other uses table columns. The output for both statements is shown in Figure 3-10.

Listing 3-14.  Examples Using the IIF statement

--Example 1. IIF Statement Using Variables
  
DECLARE @valueA int  =  85
DECLARE @valueB int  =  45
  
SELECT IIF (@valueA  <  @valueB, 'True', 'False') AS Result
  
--Example 2. IIF Statement Using Table Column
  
SELECT IIF (Name in ('Alberta', 'British Columbia'), 'Canada', Name)
FROM [Person].[StateProvince]

CHOOSE

Another logical function introduced in SQL Server 2012 is the CHOOSE function. The CHOOSE function allows you to select a member of an array based on an integer index value. Simply put, the CHOOSE function lets you select a member from a list. The member you select can either be based off a static index value or computed value. The syntax for the CHOOSE function is as follows:

CHOOSE ( index, val_1, val_2 [, val_n ] )

If the index value is not an integer (let’s say it’s a decimal), then SQL will convert it to an integer. If the index value is out of range for the index, then the function will return a NULL. Listing 3-15 shows a simple example and Figure 3-11 shows the output. The example uses the integer value of the PhoneNumberTypeID to determine the type of phone. In this case the phone type is defined in the table so a CHOOSE function would not be necessary but in other cases the value may not be defined.

Listing 3-15.  Example Using the CHOOSE Statement

SELECT p.FirstName,
       pp.PhoneNumber,
       CHOOSE(pp.PhoneNumberTypeID, 'Cell', 'Home', 'Work') 'Phone Type'
FROM Person.Person p
JOIN Person.PersonPhone pp
ON p.BusinessEntityID  =  pp.BusinessEntityID

COALESCE and NULLIF

The COALESCE function takes a list of expressions as arguments and returns the first non-NULL value from the list. The COALESCE function is defined by ISO as shorthand for the following equivalent searched CASE expression:

CASE
WHEN expressionl IS NOT NULL THEN expression! WHEN expression IS NOT NULL THEN expression
[ .  .  . " ] END

The following COALESCE function example returns the value of MiddleName when MiddleName is not NULL, and the string No Middle Name when MiddleName is NULL:

COALESCE (MiddleName, 'No Middle Name')

The NULLIF function accepts exactly two arguments. NULLIF returns NULL if the two expressions are equal, and it returns the value of the first expression if the two expressions are not equal. NULLIF is defined by the ISO standard as equivalent to the following searched CASE expression:

CASE WHEN expressionl  =  expression2 THEN NULL
ELSE expressionl
END

NULLIF is often used in conjunction with COALESCE. Consider Listing 3-16, which combines COALESCE with NULLIF to return the string This is NULL or A if the variable @s is set to the character value A or NULL.

Listing 3-16.  Using COALESCE with NULLIF

DECLARE @s varchar(10);
SELECT @s  =  'A';
SELECT COALESCE(NULLIF(@s, 'A'), 'This is NULL or A'),

T-SQL has long had alternate functionality similar to COALESCE. Specifically, the ISNULL function accepts two parameters and returns NULL if they are equal.