New Logical Functions CHOOSE() & IFF() in Sql Server 2012

SQL Server 2012, has new features to perform Logical Functions such as CHOOSE and IIF that can be used to perform logical operations. In this tip we take a look at some basic examples of how this could be used.
  
1. CHOOSE():
             CHOOSE is one of the new built-in logical function introduced as a Part of Sql Server 2012. It returns the value at the specified index position from the list of values.

Syntax:      CHOOSE( index, value_1,value_2,value_3 [,..value_n])


 index
 Is an integer expression that represents a 1-based index into the list of         the  items following it.If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null

val_1 … val_n

List of comma separated values of any data type.

Examples:

SELECT CHOOSE(1,'babji','jaipal','naveen','jana')----ans:babji

SELECT CHOOSE(2,'babji','jaipal','naveen','jana')---ans: jaipal


SELECT CHOOSE(3,'babji','jaipal','naveen','jana')---ans: naveen

SELECT CHOOSE(4,'babji','jaipal','naveen','jana')---ans: jana

SELECT CHOOSE(5,'babji','jaipal','naveen','jana')--- ans : NULL

SELECT CHOOSE(-10,'babji','jaipal','naveen','jana') --ans: null

SELECT CHOOSE(0,'babji','jaipal','naveen','jana')----ans: null


---error
SELECT CHOOSE('TEST','babji','jaipal','naveen','jana')

Result:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘TEST’ to data type int.

using decimals:
 SELECT CHOOSE(0,'babji','jaipal','naveen','jana')----ans: null
 SELECT CHOOSE(1.2,'babji','jaipal','naveen','jana')----ans:  babji
SELECT CHOOSE(1.8,'babji','jaipal','naveen','jana')----ans: babji 
 

SELECT 
  DISTINCT(FirstName + ' ' + LastName) AS Name
 ,DATEPART(DD, ModifiedDate) AS [Date]
 ,CHOOSE(DATEPART(MM,ModifiedDate),'January','February','March','April','May','June',
  'July','August','September','October','November','December')[Month]
 ,DATEPART(YYYY, ModifiedDate) AS [Year]
  FROM [Person].[Person] 
  ORDER BY Name ASC
  GO

--above exemple we are using case also

SELECT 
    DATEPART(DD,ModifiedDate) as [date]
    ,case datepart(mm,modifieddate)
      when   1  then 'january'
      when  2  then 'february'
      when  3  then 'march'
      when  4  then 'april'
      when   5  then 'may'
      when   6  then 'june'
      when   7  then 'july'
      when   8  then 'august'
      when   9  then 'september'
      when   10 then 'oct'
      when   11  then 'nov'
      when   12  then 'dec'
    else null end as [year]
 from   dbo.person with(nolock)
       

IFF( )  Logical Function: 

• IFF() logical function has to introduced in sql server 2012

• IIF() is the shorthand way of writing CASE statement or IF-ELSE statement. In-fact if we see the execution plan the IIF() function internally translates to a CASE statement.

 it has three arguments

Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

 SYNTAX:
      

IIF ( boolean_expression, true_value, false_value )

  boolean_expression:

• A valid Boolean expression.

• If this argument is not a Boolean expression, then a syntax error is raised.

 true_val