SQL Server IIF Function

---

SQL Server IIF Function:-

SQL IIF function is the new built-in Logical function introduced in SQL Server 2012 and it is consider as the shorthand way of writing IF Else, and CASE statements in SQL Server. IIF() function accepts three arguments where first argument is the Boolean expression and If the expression results is TRUE then it returns second argument as result otherwise,it returns third argument as output.

SQL Server IIF Logical Function Syntax:-

IIF(boolean_expression, true_value, false_value)

If the given Boolean expression is true then it will return True_Value and if the condition is false then it will return False_Value .

In this syntax:-

boolean_expression :- is an expression to be evaluated and It should be a valid Boolean expression otherwise the function will raise an error.
true_value :- it returns if the boolean_expression evaluates to true.
false_value :– it returns if the boolean_expression evaluates to false.

---

We can replace CASE and IF…ELSE statement using IIF()Logical Function.

CASE 
    WHEN boolean_expression 
        THEN true_value
    ELSE
        false_value
END

---

Example of case expression:-

DECLARE @First_number INT = 50
DECLARE @Second_number INT = 60

SELECT CASE WHEN @First_number > @Second_number THEN '50'  ELSE '60' END AS [Output Using Case expression]

Example of IF…ELSE statement:-

DECLARE @First_number INT = 50
DECLARE @Second_number INT = 60

IF(@First_number >  @Second_number)
   BEGIN
    SELECT @First_number AS [Output Using If else expression]
   END
ELSE
   BEGIN
    SELECT @Second_number [Output Using If else expression]
   END

SQL IIF Logical Function Example:

DECLARE @First_number INT = 10
DECLARE @Second_number INT = 20
SELECT IIF ( @First_number > @Second_number , '50', '60' ) 
 AS [Output Using IIF Logical Function]
GO

Outputs:-

 

---