SQL 2012 IIF() Function and CASE Expression

By SQLRx Admin | SQL Server

Feb 07

A new function available in SQL Server 2012 is IIF. IIF is very similar to the CASE expression and is in essence a shorthand way to write a CASE function. The IIF function returns one of two values depending on whether the first expression evaluates to TRUE or FALSE. The IIF function can be nested up to 10 levels just like the CASE expression. IIF can only evaluate two values and no more.

Here is a simple example of each:

— IIF

DECLARE @a int = 2012, @b int = 2013;

SELECT IIF( @a > @b, ‘Out with the old.’, ‘In with the new.’ ) AS HappyNewYear;

GO

— CASE

DECLARE @a int = 2012, @b int = 2013;

SELECT (CASE WHEN @a > @b THEN ‘Out with the old.’ ELSE ‘In with the new.’ END) AS HappyNewYear;

GO

For more information:

http://msdn.microsoft.com/en-us/library/hh213574.aspx

http://msdn.microsoft.com/en-us/library/ms181765.aspx

About the Author

>