SQL Server 2012: IIF

SQL Server 2012 introduces the “IIF” function, it’s a shorthand for the CASE expression. I am sure C# developers would love it!

An example: here we are fetching the columns in the table (HumanResources.Employee) using sys.columns and sys.types

> Using CASE

USE AdventureWorks2012
GO

SELECT      col.[Name], 
            CASE col.[is_nullable] WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsNullable],
            CASE col.[is_identity] WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsIdentity],
            types.[name] as [DataType],
            col.[max_length] AS [MaxLength]
FROM sys.columns col
    INNER JOIN sys.types types ON 
    col.[user_type_id] = types.[user_type_id]
    WHERE col.[object_id] = object_id('HumanResources.Employee')
    ORDER BY col.[NAME]
GO

> Using IIF

SELECT col.[Name], 
            IIF(col.[is_nullable] = 1, 'Yes', 'No') AS [IsNullable],
            IIF(col.[is_identity] = 1, 'Yes', 'No') AS [IsIdentity],
            types.[name] as [DataType],
            col.[max_length] AS [MaxLength]
FROM sys.columns col
INNER JOIN sys.types types ON 
col.[user_type_id] = types.[user_type_id]
WHERE col.[object_id] = object_id('HumanResources.Employee')
ORDER BY col.[NAME]
GO

> Output of the two queries