SQL SERVER – Denali – Logical Function – IIF() – A Quick Introduction

In SQL Server Denali, there are two new logical functions being introduced, namely:

IIF()
CHOOSE()

Today, we will have a look at the IIF() function. This function does not need any introduction as developers have used this function in various languages from ages. This function is shorthand way for writing CASE statement. These functions take three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result.

IIF can be nested as well, which makes its usage very interesting. The limit of nesting of IIF is same as CASE statement, which is capped at 10.

Now, let us look at these examples that show how IIF() 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
GO

In above example, we can see how IIF is used instead of CASE statement and the decision is made during run time using TRY_PARSE() function. You can read more about the function TRY_PARSE() here. IIF can be used the same way as CASE statement in SELECT statement.

In the next blog post, we will discuss the difference between CONVERT and PARSE as well as TRY_CONVERT and TRY_PARSE. We will also look at a couple of interesting trivia questions.

Reference:  Pinal Dave (https://blog.sqlauthority.com)