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

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

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.

Vishwanath Dalvi
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.
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

LATEST REVIEWS

Recent Comments

error: Content is protected !!