SQL IIF – function in T-SQL language

SQL IIF function of T-SQL language, which is designed to simplify the writing of conditional constructions using the well-known expression CASE or operators IF…ELSE…THEN.

IIF is a logical function of T-SQL language, which returns one of two values depending on the result of a logical expression. This function appeared in 2012 version of SQL Server.

IIF syntax

IIF ( boolean_expression, true_value, false_value )

where,

• boolean_expression is a logical expression. If a non-logic expression is specified, a syntax error will occur;
• true_value – value which will be returned if boolean_expression has value TRUE;
• false_value – value which will be returned if boolean_expression has value FALSE.

Thus, the IIF function has 3 parameters, the first parameter specifies a logical expression, the second parameter we write the value to be returned if the logical expression returns TRUE, the third parameter is the value to be returned if the logical expression returns FALSE.

Features of the IIF function

true_value and false_value may have different data types, and the IIF function will return the result with the data type that has the highest priority.

If we consider the internal work of the IIF function, all actions in it are referred to the CASE expression and, thus, the IIF function is the absolute equivalent of the CASE expression, and it is created and applied for faster and easier writing of logical expressions using CASE.

Parameters true_value and false_value can not simultaneously contain values NULL, at least one of these values must be different from NULL, otherwise, ie if you specify NULL for both of these parameters, we get approximately the following error:

Message 8133, level 16, state 1, line 1

At least one of the resulting expressions in the CASE specification must differ from the NULL constant.

Examples of using IIF function in T-SQL

Let us now look at some examples of how IIF works.

Example 1 – Easy to use IIF function

In this example we will specify 1 < 2 as a logical expression, i.e. we will ask 1 less than two, as a result we will get the value of the second parameter, in our case TRUE, as it is obvious that our logical expression returns the truth, i.e. 1 is really less than 2.

 SELECT IIF(1 < 2, 'TRUE', 'FALSE') AS Result;

However, if we only change the logical expression, for example, we specify 1 > 2, then the result will be the value of the third parameter, ie FALSE, as 1 can not be more than 2.

   SELECT IIF(1 > 2, 'TRUE', 'FALSE') AS Result;

Example 2 – Using IIF function with variables

Variables can be used in all parameters of IIF function, in the following example integer variables are substituted in IIF function.

DECLARE @A INT = 5,
           @B INT = 3,
           @true_value INT = 1,
           @false_value INT = 0;

   SELECT IIF ( @A >= @B, @true_value, @false_value ) AS Result;

And in this query we are substituting already text values.

DECLARE @A INT = 5,
           @B INT = 3,
           @true_value VARCHAR(10) = 'A A more than B',
           @false_value VARCHAR(10) = 'A less than B';

   SELECT IIF ( @A > @B, @true_value, @false_value ) AS Result;

Example 3 – Using IIF function with more complex logical expression

As the first parameter, i.e. a logical expression, you can specify more complex expressions that will contain different calculations. For example, in the following example we are not just checking the values of two variables, but the result of their multiplication, in this case we ask if it will be more than 10.

DECLARE @A INT = 5,
           @B INT = 3,
           @true_value INT = 1,
           @false_value INT = 0;
   SELECT IIF ( @A * @B > 10, @true_value, @false_value ) AS Result;

Example 4 – Nested IIF

The IIF function can be used in an expression within another IIF function. For example, in the following SQL query we want to know which category the product belongs to by its name. For this purpose, we first compare the value of the product name with one text value, and then, depending on the result, continue to compare, but only with another text value.

 DECLARE @ProductName VARCHAR(100) = 'Monitor';
   SELECT IIF ( @ProductName = 'Smartphone',
               'Mobile devices',
               IIF( @ProductName = 'Monitor', 'PC Accessories', 'No category')
              ) AS Result;

In this case, the first IIF we have returned FALSE, so we moved to the third parameter, where we have another IIF, and he in turn returned TRUE, so the result is “PC Accessories”.

However, if we specify a value in a variable that doesn’t match the category we need, we get a value of “Without Category”.

 DECLARE @ProductName VARCHAR(100) = 'Desk';
   SELECT IIF ( @ProductName = ''Smartphone',
                'Mobile devices,
                IIF( @ProductName = 'Monitor', 'PC Accessories', 'No category')
              ) AS Result;

Example 5 – Using an IIF function in a table query

The IIF function, like other functions, can be used in a regular SQL query to a table or any other table expression. In other words, you can pass column names to the IIF function as parameters.

In this example, based on the product category, we increase its price.

   SELECT Category,
          ProductName,
          Price,
          Price + IIF(Category = 1, 10, 15) AS TotalPrice
   FROM Goods;

That’s all for today, we have considered the IIF function of T-SQL language, which simplifies the writing of various conditional constructions using CASE.

IIF function in SQL Server