Intro
Today I’m looking at the new TSQL function IIF that is available in SQL Server 2011 ”Denali”. IIF stand for “Inline IF”. IIF is a shorthand way for writing a CASE statement in TSQL.
MSDN Description
Returns one of two values, depending on whether the Boolean expression evaluates to true or false.
Syntax Format: IIF ( boolean_expression, true_value, false_value )
The first parameter is a Boolean expression e.g. Age > 21. The second parameter is return value if the expression is true and the third parameter is the return value if the expression is false.
TSQL Example
Basic example for IIF function:

DECLARE @age INT = 18;
SELECT IIF(@age > 21,'Allowed','Not allowed');

You can also have nested IIF. Nested IIF can only be nested up to a maximum level of 10. This is because the CASE statement has the same limitation.

DECLARE @age INT = 28,
        @firsttime BIT = 1;
SELECT IIF(@age > 21,
           IIF(@firsttime = 1, 'Free drink', 'No free drink'),
           'Not allowed');

Practical Example

My practical examples are using the AdventureWorks2008R2 database for Denali which is available here. Let look at a practical example for IIF function:

SELECT pdt.Name,
       IIF(SUM(piy.Quantity) < pdt.SafetyStockLevel, 'Low Stock', 'Normal Stock') AS 'Stock Status'
  FROM [AdventureWorks2008R2].[Production].[Product] pdt
  LEFT OUTER JOIN 
       [AdventureWorks2008R2].[Production].[ProductInventory] piy ON pdt.ProductID = piy.ProductID
 GROUP BY pdt.Name, pdt.SafetyStockLevel

As you can see the IIF function makes it allot easier to use than the CASE statement and helps to make your TSQL code smaller and more understandable. Let me know what you think about this function addition in SQL Server 2011 Denali or if you have any questions.

Cheerio!