New Logical Functions in SQL Server 2012

Following are the New Logical functions which are introduced in SQL Server 2012

a.      CHOOSE

b.      IIF

Now we look at the each function in detail.

CHOOSE:

    Returns the item at a specified index from a list.

    Syntax:

     CHOOSE ( index, val_1, val_2 [, val_n ] )

    Where Val_1,Val_2…are the list of item of any datatype and index specifies which Item to be returned from list.

    Example:

    Select CHOOSE(2,'Pranay','P','April') as Result;

    And result would be

    

    

   Now we look at the another example

  USE AdventureWorks2012;

  GO

  SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1

  FROM Production.ProductCategory;

  

In previous version of SQL sever, to get the same result set as above we need to use the case statement.

SELECT CASE PRODUCTCATEGORYID

          WHEN 1 THEN 'A'

          WHEN 2 THEN 'B'

          WHEN 3 THEN 'C'

          WHEN 4 THEN 'D'

          END AS EXPRESSION1,PRODUCTCATEGORYID

          FROM PRODUCTION.PRODUCTCATEGORY

the result of above query will be same as CHOOSE function’s result set.

We cannot use the Comparison statement in CHOOSE statemet and CHOOSE cannot be used in the WHERE Clause.

IIF:

IIF Function behaves exactly same as IIF funtion in .NET or in any other programming language.

It returns one of the two two values depending on the boolean expression.

Syntax:

  IIF ( boolean_expression, true_value, false_value )

Example:

 SELECT IIF ( 10 >20, 'TRUE', 'FALSE' ) AS Result;

SELECT IIF ( 10 < 20, 'TRUE', 'FALSE' ) AS Result;