Home > SQLServer, SQLServer 2012 > SQL Server – "Denali" – Conditional Operator/Logical Function – IIF()

SQL Server – "Denali" – Conditional Operator/Logical Function – IIF()


Denali introduces a new function IIF(), which is a shorthand way of writing a CASE expression. IIF() evaluates specified expression and returns specified true/false values. You might be already familiar with IIF() in Reporting Services and MS Access. C/C++ also has the same as "Conditional Operator".

An example to demonstrate IIF() would be to find a maximum number between two numbers, In earlier versions we need to use CASE expression to do the same:

SELECT Num1, Num2,

       CASE WHEN Num1 > Num2

            THEN Num1

       ELSE Num2 END AS 'Max'

FROM   dbo.Numbers

The same logic can be written using IIF() in "Denali" as:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT Num1, Num2,

       IIF(Num1 > Num2, Num1, Num2) AS 'Max'

FROM   dbo.Numbers

Result Set:

Num1        Num2        Max

———– ———– ———–

12          86          86

24          23          24

46          35          46

56          56          56

47          60          60

32          NULL        NULL

42          12          42

60          58          60

 

(8 row(s) affected)

As shown in the result set NULL propagates through, which can be handled using ISNULL() function:

SELECT Num1, Num2,

       IIF(ISNULL(Num1, 0) > ISNULL(Num2, 0), Num1, Num2) AS 'Max'

FROM   dbo.Numbers

Result Set:

Num1        Num2        Max

———– ———– ———–

56          56          56

47          60          60

-32         NULL        NULL

42          12          42

 

(4 row(s) affected)

IIF() can also be nested to incorporate multiple conditions, for instance, finding a maximum value out of three numbers:

SELECT Num1, Num2, Num3,

       IIF (Num1 > Num2,

              IIF (Num1 > Num3, Num1, Num3),

       Num2) AS 'Max'

FROM   dbo.Numbers3

Result Set:

Num1        Num2        Num3        Max

———– ———– ———– ———–

12          86          56          86

24          23          43          43

46          35          87          87

56          56          12          56

47          60          56          60

32          23          89          89

42          12          76          76

60          58          23          60

 

(8 row(s) affected)

IIF() is translated into CASE expression, and thus it can be nested up to only 10 levels. Error message will confirm that it has been translated to CASE expression. ?

Msg 125, Level 15, State 2, Line 6

Case expressions may only be nested to level 10.

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.