Using the TSQL IIF Function

Here is a quick video tutorial on how to use the IIF function on SQL Server 2012, SQL Server 2014 0r newer. This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the sample code from the video.


USE Master;
GO

IF EXISTS(SELECT name
            FROM sys.databases
		   WHERE name = 'QueryTraining')
BEGIN
	ALTER DATABASE [QueryTraining]
	  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [QueryTraining];
END
CREATE DATABASE [QueryTraining];
GO

USE [QueryTraining];
GO

------------------------ EXAMPLES SETUP ------------------------
-- Table to be used for training
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);

------------------------------------------------
-- IIF (2012)
USE [QueryTraining];
GO

-- 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 Info:

Additional Training on my YouTube Channel
Free SQL Query Training

Using the TSQL IIF Function

Leave a Reply Cancel reply