What is IIF?
The function ‘IIF’ is a logical function that is passed three arguments. It evaluates the first argument to a boolean expression and returns the second argument if the first argument is evaluated to ‘TRUE’, otherwise it returns the third. This is similar to the ternary notation in programming languages such as C#, which evaluates with question marks and colons. The C# expression string result = (i < 3? "less than": "more than")
would then be equivalent to the TSQL extract @Result = IIF(@i<3, "less than", "greater than")
.
What can you use IIF for?
First of all, the IIF
function is a shorter notation for CASE
-WHEN
-query constructs. Let's take the Wide World Importers database as an example and query the StockItemTransaction
table in it. Let's take a closer look at the TransactionTypeID
. We notice that the table contains only three transaction types:
SELECT DISTINCT TransactionTypeID FROM Warehouse.StockItemTransactions
If we now wanted to resolve the transaction type, we could of course execute a join to the corresponding table to resolve the name:
SELECT StockItemTransactionID ,StockItemID ,TransactionTypeName ,CustomerID ,InvoiceID ,SupplierID ,PurchaseOrderID FROM Warehouse.StockItemTransactions sit LEFT JOIN Application.TransactionTypes tt on sit.TransactionTypeID = tt.TransactionTypeID
If we don't want to join for only three values in a column, we could use a CASE
statement instead:
SELECT StockItemTransactionID ,StockItemID ,TransactionTypeName = CASE TransactionTypeID WHEN 10 THEN 'Stock Issue' WHEN 11 THEN 'Stock Receipt' WHEN 12 THEN 'Stock Adjustment at Stocktake' ELSE 'Unknown' END ,CustomerID ,InvoiceID ,SupplierID ,PurchaseOrderID FROM Warehouse.StockItemTransactions
That in turn we could paraphrase to:
SELECT StockItemTransactionID ,StockItemID ,TransactionTypeName = IIF(TransactionTypeID = 10, 'Stock Issue', IIF(TransactionTypeID = 11, 'Stock Receipt', IIF(TransactionTypeID = 12, 'Stock Adjustment at Stocktake', 'Unknown'))) ,CustomerID ,InvoiceID ,SupplierID ,PurchaseOrderID FROM Warehouse.StockItemTransactions
Why all this effort, when we can map everything with CASE
statements? Very simple: because an inline function often reads better than a CASE
statement. As an example we take the following query:
SELECT NStockIssues = SUM(IIF(TransactionTypeID = 10, 1, 0)) ,NStockReceipts = SUM(IIF(TransactionTypeID = 11, 1, 0)) ,NStockAdjustments = SUM(IIF(TransactionTypeID = 12, 1, 0)) FROM Warehouse.StockItemTransactions
Here many ninjas find the interaction of IIF
with SUM
much more readable than the corresponding CASE
formulation:
SELECT NStockIssues = SUM(CASE TransactionTypeID WHEN 10 THEN 1 ELSE 0 END) ,NStockReceipts = SUM(CASE TransactionTypeID WHEN 11 THEN 1 ELSE 0 END) ,NStockAdjustments = SUM(CASE TransactionTypeID WHEN 12 THEN 1 ELSE 0 END) FROM Warehouse.StockItemTransactions
Whether you prefer the first or the last form is of course a matter of taste, but you should be aware that IIF is not ANSI SQL functionality (and is only included in T-SQL since 2012, rumor has it that this might have something to do with the integration of Access databases into SQL Server). So if compatibility with other database systems is important to you (or you are using older SQL Server versions), you should avoid the short form with IIF and write CASE statements instead.