IIF() Function in SQL Server
Last Updated : 14 Apr, 2023
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.
Similar Reads
MIN() Function in SQL Server
MIN() : This function in SQL Server is used to find the value that is minimum in the group of values stated. Features : This function is used to find the minimum value.This function comes under Numeric Functions.This function accepts only one parameter namely expression. Syntax : MIN(expression) Par
2 min read
RANK() Function in SQL Server
The RANK() function is a powerful window function in SQL Server used to assign a rank to each row within a result set. It is particularly useful when we need to assign a rank to a group of rows based on some sorting criteria and want to differentiate between rows that have the same values. Unlike ot
5 min read
SQL Server LEN() Function
SQL SERVER LEN() function calculates the number of characters of an input string, excluding the trailing spaces. LEN() Function in SQL ServerThe LEN function in the SQL Server fetches the number of characters in a string. It counts the preceding spaces but not the trailing spaces. For eg, 'SQL SERVE
2 min read
LOG() Function in SQL Server
The LOG() function returns the logarithm of a specified number or the logarithm of the number to the specified base. Syntax : LOG(number, base) Parameter : LOG() function accepts two-parameters as mentioned above and described below. number - This parameter hold a number which is greater than 0. bas
1 min read
DAY() Function in SQL Server
DAY() function : This function in SQL Server is used to return the day of the month i.e, from 1st to 31st for date stated. Features : This function is used to find the day of the month for a date specified. This function comes under Date Functions. This function accepts only one parameter i.e, date.
2 min read
SUM() Function in SQL Server
The SUM() function in SQL Server is an essential aggregate function used to calculate the total sum of values in a numeric column. It aggregates data by summing up all values in the specified column for the rows that match the criteria of the query. In this article, We will learn about SUM() Functio
3 min read
MONTH() Function in SQL Server
MONTH() function : This function in SQL Server is used to return the month of the year i.e, from 1 to 12 for a date stated. Features : This function is used to find the month of the year for a date specified. This function comes under Date Functions. This function accepts only one parameter i.e, dat
2 min read
SQL Server ISNULL() Function
The ISNULL() function in SQL Server is a powerful tool for handling NULL values in our database queries. It allows us to replace NULL values with a specified replacement value, ensuring that your queries return meaningful results even when data is missing. In this article, We will learn about the SQ
4 min read
STR() Function in SQL Server
The STR() function converts a numeric value to a character value. Syntax : STR(float_expression [, length [, decimal]]) Parameter : This method accepts three parameters as mentioned above and described below : float_expression : It is a numeric expression that evaluates to an approximate number with
1 min read
LTRIM() Function in SQL Server
The LTRIM() function in SQL Server removes all the space characters found on the left-hand side of the string. It removes the leading spaces from a string, SyntaxThe LTRIM function for SQL Server syntax is: LTRIM(string, [trim_string]) Parameter: string - The string from which the leading space char
2 min read