SQL Server 2012: How to Use IIF() Logical Function

Posted February 19, 2013 by Vishwanath Dalvi in Database

SQL Server 2012 has introduced the new logical function IIF(). The behavior of function IIF() is quite similar to CASE and IF statements in SQL Server. Using IIF(), you can use fewer lines of code, and your code will be more readable.

Let’s explore the IIF() function with some examples.

IIF() – Function returns one of the two values, depending upon whether the specified boolean expression evaluates to true or false.

Syntax : IIF ( boolean_expression, true_value, false_value )

 
• The first argument (boolean_expression) accepts the boolean expression to be evaluated.
• Function returns second argument (true_value), if boolean expression evaluates to true.
• Function will return third argument (false_value) means boolean expression has failed.

Let’s see some quick SQL queries with function IIF()

Example 1.1 : Compare two numbers with IIF()

SELECT IIF(1 > 0,'True','False') AS 'Output'
GO

Output
------
True

 
Example 1.2: Compare two dates with IIF()

SELECT IIF('10/15/2012' > '01/01/2012','Yes','No') AS 'Output'
GO

Output
------
Yes

 
Example 1.3: Compare two integer variables with IIF()

DECLARE @num1 AS INT = 150
DECLARE @num2 AS INT = 100

SELECT IIF( @num1 < @num2, 'True', 'False') AS 'Output'
GO

Output
------
False

 
Example 1.4: Compare two strings with IIF()
 
Most of you are wondering why we should use IIF() over CASE and IF statements.

IIF() looks more readable, is simpler to craft, and has fewer line of codes compared using CASE and IF statements.

Let us contrast the various methods of comparing two strings by using IF, CASE, and IIF() Function.

DECLARE @str as varchar(20) = 'tech-recipes'
if(@str = 'tech-recipes') 
select 'Yes' AS 'OUTPUT'
ELSE
select 'No' AS 'OUTPUT'
GO

OUTPUT
------
Yes

 

DECLARE @str AS varchar(20) = 'tech-recipes'
select CASE when @str='tech-recipes' 
THEN 'Yes'
ELSE 'No'
END AS 'Output'
GO

OUTPUT
------
Yes

 

DECLARE @str as varchar(20) = 'tech-recipes'
select IIF(@str = 'tech-recipes', 'yes', 'no') as OUTPUT
GO

OUTPUT
------
Yes

 
The above three queries using IF, CASE, and IIF() Function do the same thing and return the same result. However, using IIF() is easier and more readable.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Joe Celko

    Never use this piece of dialect. It does not follow ANSI/ISO Standards, it is weak and redundant. All it does is tell the world that you do not know SQL and that you miss using a spreadsheet. I use it as a diagnostic for bad SQL programming because I know to look for a spreadsheet mindset.