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
------
TrueExample 1.2: Compare two dates with IIF()
SELECT IIF('10/15/2012' > '01/01/2012','Yes','No') AS 'Output'
GO
Output
------
YesExample 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
------
FalseExample 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
------
YesDECLARE @str AS varchar(20) = 'tech-recipes'
select CASE when @str='tech-recipes'
THEN 'Yes'
ELSE 'No'
END AS 'Output'
GO
OUTPUT
------
YesDECLARE @str as varchar(20) = 'tech-recipes'
select IIF(@str = 'tech-recipes', 'yes', 'no') as OUTPUT
GO
OUTPUT
------
YesThe 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.
SQL Server 2012: How to Use CHOOSE() Logical Function
SQL Server 2012 introduced the powerful new CHOOSE() function. This tech-recipe will explore its use and common mistakes through some simple examples.
The CHOOSE() function returns the item at a specified index. The behavior is the same as the list of items in array and uses array index to get the item at specified index.
Syntax
Syntax: CHOOSE ( index, val_1, val_2 [, val_n ] )• Index argument accepts integer expression, and it has to start with 1-based index.
• Val_1 to val_n list of items.
Examples
Example 1.1 – CHOOSE() with index
SELECT CHOOSE(0 ,'tech', 'recipes', 'cookbook' ) AS 'index_as_0'
GO
index_as_0
----------
NULLThis shows NULL because CHOOSE() accepts index arguments and must start with one or a value greater than one.
Example 1.2 – CHOOSE() with valid index value
SELECT CHOOSE( 1 ,'tech' ,'recipes' ,'cookbook' ) AS 'index_as_1'
GO
index_as_1
----------
techThis returns the ‘tech’ item as result from the value list because we have passed one as index argument.
Example 1.3 – CHOOSE() with index greater than number of items in the list
SELECT CHOOSE(4,'tech', 'recipes', 'cookbook') AS 'index_as_4'
GO
index_as_4
----------
NULLThis returns NULL because we are passing index argument as four, and we do not have any item at the fourth position.
Example 1.4 – CHOOSE() with decimal index
SELECT CHOOSE(2.9, 'tech', 'recipes', 'cookbook') AS 'decimal_index'
GO
decimal_index
-------------
recipesThis does not return any error. It shows ‘recipes’ in result. When you are passing the first argument as 2.9, it converts a decimal value into an integer, treats the decimal value 2.9 as 2, and shows the ‘recipes’ item as the result.
Example 1.5 CHOOSE() with negative index
SELECT CHOOSE(-2, 'tech', 'recipes', 'cookbook') AS 'Negative_index'
GO
Negative_index
--------------
NULLThis results in NULL because you are passing the first argument as a negative value which violates the syntax. Always make sure you always pass the positive integer value.
Example 1.6 CHOOSE() with variable
DECLARE @index AS INT = 3
SELECT CHOOSE(@index, 'tech', 'recipes', 'cookbook') AS 'Index_as_Variable'
GO
Index_as_Variable
-----------------
cookbookWe have declared an int variable @index with value three and have passed the variable as index value. It is showing ‘cookbook’ because it is the third index item in the list.
Choose in SQL Server 2012
Overview
SQL Server 2012 has introduced 2 new logical functions. IIF & Choose.Let's go over CHOOSE function. It returns a value at specific index from a list.
Scenarios & Example
- Basic Use
SELECT CHOOSE(3, 'SQL 2005', 'SQL 2008', 'SQL 2012')
(No column name)
|
SQL 2012
|
So we can see that, it gives 3rd value from the set of value we have provided.
Let's test if we give some unusual index values
SELECT 'CASE 1.1' [Case], CHOOSE(1.1, 'SQL 2005', 'SQL 2008', 'SQL 2012') [Value]
UNION ALL
SELECT 'CASE 1.9' , CHOOSE(1.9, 'SQL 2005', 'SQL 2008', 'SQL 2012')
UNION ALL
SELECT 'CASE -3' , CHOOSE(-3, 'SQL 2005', 'SQL 2008', 'SQL 2012')
UNION ALL
SELECT 'CASE 5' , CHOOSE(5, 'SQL 2005', 'SQL 2008', 'SQL 2012')
Case
|
Value
|
CASE 1.1
|
SQL 2005
|
CASE 1.9
|
SQL 2005
|
CASE -3
|
NULL
|
CASE 5
|
NULL
|
- Advance Use
Let's say, we have a date stored in a table, we want to know whether it is weekday/weekend, then this is how we can write using CHOOSE.
SELECT EmployeeName,EmployeeDOB, DATENAME(DW,EmployeeDOB),DATEPART(DW,EmployeeDOB),
CHOOSE(DATEPART(DW,EmployeeDOB), 'WEEKEND', 'Weekday', 'Weekday', 'Weekday','Weekday', 'Weekday', 'WEEKEND') FROM Employee
EmployeeName
|
EmployeeDOB
|
(No column name)
|
(No column name)
|
(No column name)
|
John
|
4/4/1989
|
Tuesday
|
3
|
Weekday
|
Rob
|
5/28/1990
|
Monday
|
2
|
Weekday
|
Bob
|
3/16/1991
|
Saturday
|
7
|
WEEKEND
|
Ryan
|
12/5/1983
|
Monday
|
2
|
Weekday
|
Lisa
|
9/14/1979
|
Friday
|
6
|
Weekday
|
We can also write the same thing using CASE statement but the only thing CHOOSE will be easier to write than CASE.
w.r.t Performance, there is no difference either you use CASE or CHOOSE, both are same. CHOOSE internally uses CASE only.
