IIF

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.

References

• Documentation from Microsoft