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

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!