Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Chris Di LulloSr. IT Pro Marketing Manager Twitter | LinkedIn Pierre Roman Twitter | LinkedIn Mitch Garvis Twitter | LinkedIn Anthony Bartolo Twitter | LinkedIn
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')
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')
Executing the above statement will return the following result:
JOHN IS OLDER THAN JULIE BY 6 YEARS
Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.
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!