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;

SQL SERVER - Denali - Logical Function - IIF() - A Quick Introduction iif1

Example 2: IIF simulated by CASE statement

SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result;

SQL SERVER - Denali - Logical Function - IIF() - A Quick Introduction iif2

Example 3: IIF with NULL

SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result;

SQL SERVER - Denali - Logical Function - IIF() - A Quick Introduction iif3

Example 4: Nested IIF

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

SQL SERVER - Denali - Logical Function - IIF() - A Quick Introduction iif4

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

SQL SERVER - Denali - Logical Function - IIF() - A Quick Introduction iif5

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)

,
Previous Post
SQL SERVER – Denali – Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()
Next Post
SQL SERVER 2012 – Logical Function CHOOSE() – A Quick Introduction

Related Posts

12 Comments. Leave new

  • shatrughna kumar
    September 10, 2011 8:59 am

    Hi Sir,
    Your Denali articles series looking very interesting to me.
    My Denali toolbox has now filled with functions PARSE(),TRY_PARSE(),TRY_CONVERT() ,IIF() and waiting for CHOOSE().

    Reply
    • 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

      Reply
  • Agree with shatrughna kumar …

    All the Denali articles are really interesting.

    Reply
  • When is Denali expected to be released? Seems like it has lot of useful features…

    Reply
  • Rob Kellington (@kellington)
    September 10, 2011 8:42 pm

    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.

    Reply
    • 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.

      Reply
      • 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?

  • Pinal there seems problem with 32bit version of denali whereas 64 bit version have this function. Thanks –Jeetenra

    Reply
  • Wow !

    That is a good one. Thanks for sharing sir.

    Reply
  • Can we call a function or statement instead of values?

    Reply
  • HI Pinal,
    There is seems to be typing mistake in example 3. Please correct it.

    Reply
  • IIF only evaluates the third argument when the statement is false:
    SELECT iif(1> 9
    SELECT iif(1>2, 3*3, 3/0) — >> Error

    Reply

Leave a Reply

Menu