IIF() is one of the new built-in logical function introduced as a Part of Sql Server 2012. IIF() is the shorthand way of writing CASE statement or IF-ELSE statement. In-fact if we see the execution plan the IIF() function internally translates to a CASE statement.
IIF() function takes three arguments, first argument should be a Boolean expression otherwise it raises an exception. If Boolean expression evaluates to TRUE then Second argument will be the result otherwise Third argument will be the result. This functions result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.
Syntax: IIF ( boolean_expression, true_value, false_value )
Now let us understand this function with examples:
Example 1: Comparing two Integer numbers
SELECT IIF(1 > 10, 'TRUE', 'FALSE' )
Example 2: Comparing two Integer Variables
DECLARE @Marks INT = 60, @Minimum INT = 35 SELECT IIF(@Marks >= @Minimum, 'PASS', 'FAIL' )
Example 3: Comparing Strings
DECLARE @NAME Varchar(50) = 'Basav' SELECT IIF(@NAME IN ('Basav','Kalpana'), 'Likes SQL', 'Likes ORACLE' )
Result: Likes SQL
Example 4: Result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.
SELECT IIF(1 > 10, 1.5, 40)
Example 5: Nested IIF() Statement.
DECLARE @Percentage AS NUMERIC(5,2) = 71 SELECT IIF(@Percentage >= 70, 'Distinction', IIF(@Percentage>=35 AND @Percentage<70, 'Pass', 'Fail'))
Note: As IIF() statement internally translates to an CASE statement, so the Max Nesting level is 10.
Example 6: IIF() with both return value arguments value as NULL constant
SELECT IIF(1 > 2, NULL, NULL)
Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant
Example 7: IIF() with on of the return value argument value as NULL constant
SELECT IIF(1 > 2, NULL, 'NO')
Example 8: IIF() with both return value arguments are variables with value NULL
DECLARE @A INT = NULL, @B INT = NULL SELECT IIF(1 > 2, @A, @B)
You may also like to read other LOGICAL FUNCTION CHOOSE() introduced in SQL SERVER 2012
Please correct me, if my understanding is wrong. Comments are always welcome.
You may like to read the below new built-in function’s introduced in Sql Server 2012:
|New Built in Functions introduced in Sql Server|
|DATE AND TIME FUNCTIONS|
5 thoughts on “IIF() LOGICAL FUNCTION IN SQL SERVER 2012”
Example 5 will result into ‘Distinction’ and not ‘Pass’
Thanks Hardik for pointing out this typo. I have corrected it.
it is very good article of SQL – Server 2012
I have converted to sql server 2014 but none of the new functions work. e.g. FORMAT, I get the following message “‘Format’ is not a recognized built-in function name.” – so what am I doing wrong? Any help would really be appreciated.