SQL Server World

In a nutshell, IFF function is a shorthand for Case function. Here is the example to illustrate its use and syntax.

Create database myDB;
Go
Use myDB;
Go
Create table Employees (id int, name nvarchar(30), city nvarchar(30), region nvarchar(30), country nvarchar(30));
Go

Insert some records to play with.

Insert into Employees select 1,’John Abraham’,’Seattle’, ‘WA’, ‘USA’
union all Select 2, ‘Bill Clinton’, ‘Tacoma’, ‘WA’, ‘USA’
union all Select 3, ‘Bill Gates’, ‘Kirkland’, ‘WA’, ‘USA’
union all Select 4, ‘Al Gore’, ‘Redmond’, ‘WA’, ‘USA’
union all Select 5, ‘Queen Elizabeth’, ‘London’, NULL, ‘UK’
union all Select 6, ‘Sherlock Holmes’, ‘London’, NULL, ‘UK’

Select first using Case statement and then IFF statement. Notice IFF keyword replaces 3 keywords in case statement.

SELECT city, region, (case when country =’USA’ then ‘United Sataes’ else ‘United Kingdom’ end) AS countryName from Employees;

GO

SELECT city, region, IIF( country=’USA’, ‘United States’, ‘United Kingdom’) AS countryName from Employees ;
GO

For more information go here http://sqlrx.wordpress.com/2013/02/07/sql-2012-iif-function-and-case-expression/