SQL Server 2012 IIF Statement

SQL Server 2012 introduces the IIF statement. It is very similar to the IIF statement in MS Access.

I have extracted the IIF part of my presentation on Whats new in SQL Server 2012, and turned it into a video with a demo of how to use the IIF statement. The IIF statement is a quick shortcut to simple CASE statements.

Here is the T-SQL references in the video:

USE [Master];
CREATE DATABASE [tsql2012];
GO

USE [tsql2012];

-- Table to be used by Over Clause Rows/Range
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);

USE [tsql2012];
-- first lets look at the REVENUE table
SELECT *
FROM Revenue;

-------------------------------------------------------------------

-- IIF
-- http://stevestedman.com/?p=1578
-- assume we want to display an indicator to see if we are above
-- or below average. First we start with the average over departmentID
SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE
ORDER BY DepartmentID, year;
-- without IIF using the CASE statement we would get the following
-- http://stevestedman.com/?p=1578
SELECT Year, DepartmentID, Revenue, AverageRevenue,
CASE WHEN Revenue > AverageRevenue
THEN 'Better Than Average'
ELSE 'Not Better'
END as Ranking
FROM (SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE ) as t
ORDER BY DepartmentID, year;

-- now the same functionality using IIF and simplifying the code
-- http://stevestedman.com/?p=1578
SELECT Year, DepartmentID, Revenue, AverageRevenue,
iif(Revenue > AverageRevenue, 'Better Than Average', 'Not Better') as Ranking
FROM (SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE ) as t
ORDER BY DepartmentID, year;

----------------------------------
SELECT IIF(3=2,'true',NULL) -- ERROR ?

----------------------------------

SELECT IIF(1=7,NULL,NULL) -- ERROR !
--Do you think that IIF actually uses the CASE statement?

SQL Server 2012 IIF Statement

Leave a Reply Cancel reply