T-SQL IIF

Introduction to T-SQL IIF

T-SQL IIF is defined as, it is a logical function that can have another way to write the CASE expression; it has three parameters 1st is the Boolean expression; if it is true, then it gives back the 2nd parameter as a value; else, it provides the 3rd parameter if the condition gets false and the result depends on the estimation. We can say that the 1st argument is assessed for providing either true or false value. The IIF function in T-SQL has been outlined to clarify the common expression CASE or by using the operators such as IF…ELSE…THEN.

Overview of T-SQL IIF

The IIF() function can utilize the CASE statement format as given in below screenshot:

Code:

SELECT CASE Expression
When Expression1 Then result1
When Expression2 Then result2
…
ELSE Result
END

We can utilize the syntax of this function having Boolean expression, true value, and false value as its parameter in which the first parameter can be assessed to get the result; if the expression gets true, then it will give a value that is in the second parameter, and if the expression false then it will give false as the third parameter, the T-SQL IIF statement is the new path for writing the CASE statement which has been connected to the given conditions, as we say IIF () function provides a quick way to write a new CASE statement by using IF…ELSE logic is because T-SQL can internally transform the statement into the CASE statement, which gets implemented.

T-SQL IIF Statement

We can use the CASE statement to give back a result depending on the described conditions, as it is similar to the IF…ELSE…THEN statements which can assess the expression and give back the output, the CASE statement can be helpful in all versions of T-SQL, the statement can be used for comparing integer values by using a variable, string, table column, aggregate function, and it may have nested if.

1. IIF statement using integer values

Let us see the example having a Boolean expression that can give back a true value if expression is true, and it will give back a false value when the condition is false.

Code:

SELECT IIF (4 > 5, 'TRUE', 'FALSE');

Output:

In this expression, we want to describe that if the expression (4 > 5) is true, then it will give back true as the second parameter, and if the expression is false, then it gives back a false result, as shown in the above screenshot.

2. IIF statement with variables

Let us see the example by using two integer variables and assigned values.

Code:

DECLARE @B INT = 60, @C INT = 50
SELECT IIF (@B >= @C, 'PASS', 'FAIL')

Output:

In this example, we have utilized the two variables and assigned values to them; as the given condition gets true, it will show the result ‘PASS’ as shown in the above screenshot.

3. IIF statement for comparing two strings

We can able to define different T-SQL IIF statements.

• Ram and Sita both like mango.
• Else all other like papaya.

Code:

DECLARE @human Varchar (70) = 'Ram'
SELECT IIF (@human in ('Ram', 'Sita'),'Likes Mango', 'Likes Papaya')

Output:

T-SQL IIF Function

The IIF function can gain three parameters, it can assess the first parameter and give back a second parameter; if the first parameter is true, then it will give back a second parameter; and if the first parameter is false, then it will give back a third parameter, the IIF () function may give back a result with various data types the true and false value may have multiple data types which can be the highest priority, if we assume the internal functioning of IIF () function as it referred the CASE expression so we can say that the IIF () function may be similar to the CASE expression as we know it is generated and implemented for quick and straightforward logical expression with CASE.

Let us see the syntax of the IIF () function:

IIF(Boolean Expression, true_value, false_value)

Where,

• Boolean expression: It is a valid expression to be assessed to get an error.
• true_value: It is a value that can give back an actual value by assessing the boolean expression.
• false_value: It is also a value, but it can give back a false value by assessing the Boolean expression.

Let us see how the IIF() function is the shorthand for a CASE expression.

Code:

CASE
WHEN logical_expression
THEN
correct_result
ELSE
wrong_result
END

Let us see the simple examples of using the IIF() function:

Code:

SELECT IIF(35<55, 'YES', 'NO');

Output:

In the above example, we tried to scan the given value like 35 is less than 55; if it is true, it will show ‘YES,’ and if it is false, then it will give back ‘NO’ in the result, so as it gives back a true result as ‘YES.’

Using the above example in reverse can give back a false result such as the one given below.

Code:

SELECT IIF (35 > 55, 'YES', 'NO');

Output:

So as shown in the above screenshot, we can say that the 1st expression has 35 is less than 55, so 35>55 is false; hence by using this function, we get the ‘NO’ result as the 3rd parameter.

Now let us see an example where two conditions are similar.

Code:

SELECT IIF ('he' = 'she', 'YES', 'NO');

Output:

In this example, we try to match two strings as given in the above example; if that is equal, then we will get ‘YES,’ and if it is false, then we will get ‘NO,’ as the string is not the same so, we get the result as ‘NO.’

Conclusion

In this article, we conclude that the IIF is a logical function that can give back a value by assessing the Boolean expression, which is its first argument, so we have discussed the IIF statements and IIF functions.

Recommended Articles

This is a guide to T-SQL IIF. Here we discuss the introduction, overview, and T-SQL IIF statement and function, respectively. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. PL/SQL NOT EQUAL
4. SQL NOT IN