Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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

By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | More > Functions - System

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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.


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')

sql server CHOOSE function

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
  DISTINCT(FirstName + ' ' + LastName) AS Name
 ,DATEPART(DD, ModifiedDate) AS [Date]
 ,DATEPART(YYYY, ModifiedDate) AS [Year]
FROM [Person].[Person] 

sql server result set using the choose function in denali

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]

sql server 2012 iif logical function

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
  ,IIF(TRY_PARSE(StateProvinceCode AS INT) Between 0 AND 95,'France','Canada') AS Country
FROM Person.StateProvince 
  WHERE StateProvinceCode IN ('95','AB')

sql server 2012 iif logical function
Next Steps

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, June 29, 2016 - 10:16:06 AM - J Back To Top

Simple and to the point.

One thing missing would be to explain how these new statements are preferable over the older IF and CASE statements.



Wednesday, December 23, 2015 - 9:52:57 AM - Joe Celko Back To Top

 Good SQL programmres would luse CASE expressions instead of a weak local dialect. 


Thursday, June 27, 2013 - 12:36:29 AM - Bhavin Shah Back To Top

Thanks for introducing to new functions in SQL Server 2012.


I would like to know that if SQL server 2012 supports both functions like CASE WHEN and CHOOSE, which one should be used? and any advantages of using one over another?


Thanking You,

Bhavin Shah.

Tuesday, June 25, 2013 - 11:41:47 AM - Asif Iqbal Back To Top

That's good but what is performance difference between case and IIF.

Please reply.

Thursday, December 29, 2011 - 8:12:24 AM - Tom Kelley Back To Top

Thanks for the news.  It is nice to see SQL Server finally catching up with Microsoft Access.  :)

Learn more about SQL Server tools