Condividi tramite

The “SQL Guy” Post # 28: Introducing the New IIF() Function in SQL Server 2012

  • Articolo

In SQL Server 2012 we have added the IIF() function to the SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

 

In earlier versions of SQL Server, we have used IF..ELSE and CASE..ENDCASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.

 

 

In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value.

DECLARE @A INT=40

DECLARE @B INT=30

SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')

GO;

 

 

Executing the above T-SQL will return the following result:

 

-------------------

A IS GREATER THAN B

 

(1 row(s) affected)

 

 

In this example, we will evaluate the age of John and Julie and identify who is older between them. Please observe the use of sub functions within IIF.

DECLARE @JOHN_AGE INT=35

DECLARE @JULIE_AGE INT=29

SELECT IIF(@JOHN_AGE > @JULIE_AGE, 'JOHN IS OLDER THAN JULIE BY '+LTRIM(STR(@JOHN_AGE-@JULIE_AGE))+' YEARS', 'JULIE IS OLDER THAN JOHN BY '+LTRIM(STR(@JULIE_AGE-@JOHN_AGE))+' YEARS')

GO;

 

 

Executing the above statement will return the following result:

 

--------------------------------------------

JOHN IS OLDER THAN JULIE BY 6 YEARS

 

(1 row(s) affected)

 

 

Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.

DamirB-BlogSignature

Comments

  • Anonymous
    May 09, 2012
    Nice! I've just started digging in to doing more with SQL and the IF statement always seems like one of the first things you need to learn when doing anything! Love how elegantly it gets the job done!