IIF() function judges or evaluates the first parameter and returns the second parameter if the first parameter is true, otherwise, it returns the third parameter. IIF() function used in SQL Server to add if-else logic to queries.IIF is not supported in dedicated SQL pools in Azure Synapse Analytics.
Syntax:
IIF(boolean_value, true_1value, false_value)
Parameters Explanation
The SQL Server IIF() function has three parameters.
- boolean_value – It is a value to be judged. It must be a valid boolean value, or the function will raise an error.
- true_value – It is the value to be the result if the boolean_value to true.
- false_value – It is the value to be the result if the boolean_value to false.
The IIF() function is similar to a CASE expression
CASE WHEN boolean_expression THEN true_value ELSE false_value END
Simple IIF Example
To use the IIF() function to check if 40 < 60 :
Query:
SELECT IIF(40 < 60, 'True', 'False') AS Result ;
Output:
True
Let us assume we have below sample table named “Customer”:
Query:
CREATE TABLE Customer( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(50), LastName VARCHAR(50), Country VARCHAR(50), Age int(2), Phone int(10) ); -- Insert some sample data into the Customers table INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone) VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'), (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'), (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'), (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'), (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');
Output:
IIF() within IIF() Functions
To use IIF() function with table column. Below example uses IIF()function within IIF() functions:
Query:
SELECT IIF(Age = 24, 'Selected', IIF(Age =21, 'InProgress', IIF(Age =30, 'Rejected', IIF(Age =22, 'Selected', NULL) ) ) ) AS Status, COUNT(CustomerName) AS Count FROM Customer GROUP BY Age;
Output:
IIF() Function with Aggregate Functions
To use IIF() function with aggregate functions. Below example uses IIF()function with the SUM() function:
Query:
SELECT CustomerName, SUM(IIF(Country = 'India', 1, 0)) AS IndianCustomers, SUM(IIF(Country = 'Australia', 1, 0)) AS AustralianCustomers, SUM(IIF(Country = 'Spain', 1, 0)) AS SpanishCustomers FROM Customer GROUP BY CustomerName;
Output:
Here, the IIF() function results in 1 or 0 if the status is matched. The SUM() function results in the number of each status.