The IIF function is the new built-in Logical function introduced in SQL Server 2012. We can consider the IIF as the shorthand way of writing IF Else and CASE statements.
SQL Server IIF function will accept three arguments. The first argument is the Boolean expression, which returns true or false. If the IIF expression results in TRUE, then the second argument will be returned as a result. Otherwise, the third argument will return as output. Let us see the syntax of the IIF function:
IIF Logical Function Syntax
The syntax of the IIF in SQL Server is as follows:
IIF(Boolean_Expression, True_Value, Flase_Value)
If the given Boolean expression (or test condition) is true, then it will return True_Value. And if it is false, then False_Value will be returned.
SQL Server IIF Comparing two integers
This example will show you the working functionality of the IIF function.
SELECT IIF(10 > 5, 'TRUE', 'FALSE') AS Result;
From the below screenshot, you can observe that the Condition inside (i.e., 10 > 5) is TRUE. So, the first statement (or second argument) returned as output, which is TRUE.
IIF Compare two integer variables
In this example, we will use the IIF function to find whether the person is eligible to vote or not.
DECLARE @John_age INT = 12, @Dave_age INT = 34, @minimum INT = 18 SELECT IIF( @John_age > @minimum, 'Eligible to Vote', 'Not Eligible to Vote' ) AS Result1; SELECT IIF( @Dave_age > @minimum, 'Eligible to Vote', 'Not Eligible to Vote' ) AS Result2;
From the below screenshot you can observe that,
- Within the first SELECT statement, condition inside it (i.e., 12 > 18) is false. So, the second statement (or third argument) returned as output
- Within the second SELECT statement, condition inside it (i.e., 34 > 18) is true. So, the first statement (or second argument) returned as output
SQL Server IIF Compare two strings
In this example, we will use the IIF function to compare the string data. Here, we used the ‘%’ wildcard inside the second SELECT Statement. So, I suggest you refer LIKE article in SQL Server for further reference.
DECLARE @name AS VARCHAR(50) = 'Tutorial Gateway' SELECT IIF(@name = 'Tutorial Gateway', 'TRUE', 'FALSE') AS Result1; SELECT IIF(@name LIKE N'Tutorial%', 'TRUE', 'FALSE') AS Result2; SELECT IIF (@name = N'Tutorial', 'TRUE', 'FALSE') AS Result3;
SQL Server Nested IIF example
In this example, we will use the Nested IIF function to find whether the person is eligible to work or not.
DECLARE @age INT = 12 SELECT IIF( @age < 18, 'You are too Young to Work', IIF(@age >= 18 AND @age <= 60, 'You are eligible to Work', 'You are too Old to Work' ) ) AS Result;
- First, we declared an integer variable called @age and assigned some values.
- Within the function, the first condition verifies whether age is less than 18. If this condition is True, then it will return the first statement (or second argument) after the comma, which is You are too Young to Work
- When the first condition fails, it returns the second statement (or third argument). By using Nested IIF, we are checking one more condition here (@age >= 18 AND @age <= 60). If this condition is True, then it returns the first value after the comma, which is You are eligible to Work
- If the Nested condition fails, it will execute the value at the third argument, which is You are too Old to Work.
OUTPUT 1: Age = 12
Age = 32
Result ----- You are eligible to Work
Age = 65
Result ----- You are too Old to Work
SQL Server IIF Working with NULL values
It would be best if you were careful while you are working with NULL values inside it. The following series of examples will help you to understand the consequences.
SELECT IIF(10 > 5, NULL, NULL) AS Result;
From the above screenshot, you can observe that it is throwing an error. Saying that at least one of the result expressions (i.e., second or third argument after the Boolean_Expression) must be an expression other than the NULL constant. Let us change them accordingly and see
SELECT IIF(10 > 5, 'TRUE', NULL) AS Result1; SELECT IIF(10 > 5, NULL, 'FALSE') AS Result2;
IIF NULL values Option 2
We can overcome the errors that occurred by the NULL constants using the parameters.
DECLARE @a INT = NULL, @b INT = NULL SELECT IIF ( 10 > 5, @a, @b ) AS Result1;
Result1 ------- NULL
IIF Data type with the highest precedence
The IIF function will return the data type with the highest precedence. From the following example, the result will be the third argument (i.e., 100) of type integer, but the result displays 100.00. Because it will return the highest precedence data type.
SELECT IIF ( 15 > 50, 12.45, 100 ) AS Result;
Result ------ 100.00