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
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
SUM much more readable than the corresponding
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.