A closer look at everything related to SQL Server

TSQL 2012 IFF Function

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

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

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;


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

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: