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?
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply