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

 

 
     

What will be the result of the query below?

SELECT CASE WHEN null = 'NULL' THEN 'Yup' else 'Nope' end as Result; ?

ans: nope

SELECT CASE WHEN null=null THEN 'Yup' else 'Nope' end as Result; ?

ans:nope

SELECT CASE WHEN null is null THEN 'Yup' else 'Nope' end as Result; ?

ans: yup

what is wrong below sqlquery?

SELECT id,year(billing_date) as billing_year
 FROM invoices WITH(NOLOCK)
WHERE billing_date>=2010

-----right query

SELECT id,year(billing_date) as billing_year
 FROM invoices WITH(NOLOCK)
WHERE year(billing_date) >=2010

-----check with example 


CREATE TABLE bbbb(id INT ,name CHAR(5))

    insert into bbbb 
     values (1,'babj'),(null,null),(null,'bb'),(4,null)

 SELECT CASE WHEN name is null  THEN 'yep' ELSE 'nope' END AS results FROM bbbb

    SELECT CASE WHEN name=null  THEN 'yep' ELSE 'nope' end as results FROM bbbb

    SELECT CASE WHEN name='null'  THEN 'yep' ELSE 'nope' END AS results FROM bbbb


     SELECT * FROM bbbb (NOLOCK) WHERE id is null

     SELECT * FROM bbbb (NOLOCK) WHERE id is null or id<>1


link:   http://www.toptal.com/sql/interview-questions