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 (http://blog.SQLAuthority.com)

About these ads

17 thoughts on “SQL SERVER – Denali – Logical Function – IIF() – A Quick Introduction

    • CHOOSE() function will returns the value based on the specified index from the list of values.

      SELECT CHOOSE (1, ‘SQL 2000′,’SQL 2005′, ‘SQL 2008′, ‘SQL 2012′)

      Result
      _________
      SQL 2000

      SELECT CHOOSE (2, ‘SQL 2000′,’SQL 2005′, ‘SQL 2008′, ‘SQL 2012′)

      Result
      _________
      SQL 2005

      SELECT CHOOSE (3, ‘SQL 2000′,’SQL 2005′, ‘SQL 2008′, ‘SQL 2012′)

      Result
      _________
      SQL 2008

  1. Pinal, I do appreciate the look at the new Denali – I just struggle with “What is Microsoft Thinking”? I mean, not one of the new features seems to anything really new that I couldn’t do in SQL 2000. It appears they are just telling their engineers to ‘write new functions with CLR that replace existing functionality with new names and syntax’.

    I’m not saying I won’t use these functions. It is just that I won’t be rushing to get Denali with my money because there doesn’t appear to be any really new functionality.

    • Hi Rob,

      there are so many new functions and features. Just wait and watch this blog space for 90 days. I decided to start with something simple first and then will take it to next level – trust me it is worth every single penny.

      Again, thanks for your comment, atleast I know what is the perception and how I can help in whole matter.

      • Pinal,

        I like teh founf tion IIF. “IT is very simple and can be used in lot of queryies.
        Is the function IIF in SQL server 2008 or only in SQL server 2012?

  2. Pingback: SQL SERVER – Denali – New Functions and Shorthand for CASE Statement Journey to SQLAuthority

  3. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  4. Pingback: SQL SERVER – Denali – New Functions and Shorthand for CASE Statement « Journey to SQLAuthority

  5. Pingback: SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s