New Logical Functions in SQL Server 2012 (IIF and CHOOSE)

By: Ashish Kumar Mehta | Read Comments (1) | Print Ashish has been contributing to the MSSQLTips.com community since 2009 with over 60 tips. Related Tips: 1 | 2 | More

Problem

SQL Server 2012, code named Denali, 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.

Solution

In this tip we will discuss how to utilize the below mentioned newly introduced Logical Functions in SQL Server 2012.

1. CHOOSE Logical Function
2. IIF Logical Function

Using CHOOSE Logical Function

The CHOOSE function, which is available in SQL Server 2012, will return the item at the specified index from the list of values which are available. In this example we have 3 values and we are asking to select the third value in the list which is "SQL Server 2012".

SELECT 'New SQL Server Release' = CHOOSE(3, 'SQL Server 2008', 'SQL Server 2008 R2',
'SQL Server 2012')
GO

Let’s execute the below TSQL code which will use the CHOOSE function to return the month value using the CHOOSE logical function.

Use AdventureWorks2008R2
GO
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

Using IIF Logical Function

The IIF function, which is available in SQL Server 2012, returns one of the two values depending upon whether the Boolean expression evaluates to either True or False.

DECLARE @FirstArgument INT = 10
DECLARE @SecondArgument INT = 20
SELECT IIF ( @FirstArgument > @SecondArgument , 'TRUE', 'FALSE' ) 
 AS [Output Using IIF Logical Function]
GO

Let’s execute the below TSQL code which will use the IIF function to return the results. The logic is if the StateProvinceCode is between 0 and 95 then return France otherwise return Canada.

Use AdventureWorks2008R2
GO
SELECT 
   StateProvinceCode
  ,CountryRegionCode
  ,IIF(TRY_PARSE(StateProvinceCode AS INT) Between 0 AND 95,'France','Canada') AS Country
FROM Person.StateProvince 
  WHERE StateProvinceCode IN ('95','AB')
GO

Next Steps

• If you have not had a chance to preview SQL Server Denali, download SQL Server 2012 RC0 to start today.
• Stay tuned for more tips on SQL Server 2012 and check out these related tips:
  • Creating a SQL Server 2012 User Defined Server Level Role
  • SQL Server 2012 Contained Database Feature
  • Configuring Server Startup Parameters in SQL Server 2012

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code