The “SQL Guy” Post # 28: Introducing the New IIF() Function in SQL Server 2012
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.
