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.
