In SQL Server, there are several logical functions available to developers. One of these functions is the IIF() function, which is being introduced in SQL Server Denali. This function is a shorthand way of writing a CASE statement and can be used to make decisions based on a condition.

The IIF() function takes three arguments. If the first argument is true, it will return the second argument as the result. If the first argument is false, it will return the third argument as the result. This makes it a convenient and concise way of writing conditional statements.

Let’s take a look at some examples to understand how the IIF() function works:

Example 1: IIF Usage
SELECT IIF(-1 < 1, 'TRUE', 'FALSE') AS Result;

Example 2: IIF simulated by CASE statement
SELECT CASE WHEN -1 < 1 THEN 'TRUE' ELSE 'FALSE' END AS Result;

Example 3: IIF with NULL
SELECT CASE WHEN -1 < 1 THEN 'TRUE' ELSE 'FALSE' END AS Result;

Example 4: Nested IIF
SELECT IIF(-1 < 1, IIF(1=1, 'Inner True', 'Inner False'), 'FALSE') AS Result;

Example 5: IIF used along with TRY_PARSE and Table
USE AdventureWorks2008R2;
SELECT SP.[StateProvinceCode], A.[PostalCode], IIF(TRY_PARSE(A.[PostalCode] AS INT) IS NULL, 'Canada', 'United States') Country
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP ON SP.StateProvinceID = A.StateProvinceID;

In the above examples, we can see how the IIF() function is used instead of a CASE statement. It provides a more concise and readable way of making decisions based on conditions. In Example 5, we also see how the IIF() function can be used in conjunction with other functions like TRY_PARSE() to make runtime decisions.

It is important to note that the nesting of the IIF() function is limited to 10 levels, similar to the CASE statement. This ensures that the code remains manageable and avoids excessive complexity.

In the next blog post, we will discuss the difference between the CONVERT and PARSE functions, as well as the TRY_CONVERT and TRY_PARSE functions. We will also explore some interesting trivia questions related to SQL Server.