SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL Choose() Function in SQL Server 2011

SQL Choose() function returns an input argument from a list of input values at the desired index which is specified as an input arguement as well

CHOOSE (index, arg1, arg2 [, argN])

The first input argument to the SQL Choose() function is "index" and it is in integer data type. The input arguments can be thought as an array with index starting from 1.



SQL CHOOSE() Function Samples

In this T-SQL tutorial, SQL programmers can find Choose() function sample codes which can help to understand where and how to use SQL Choose() function.


Choose() Function and Index Argument

The input index parameter or index argument to Choose() sql function can be a static integer value, or can be an integer variable parameter.

DECLARE @index int = 2
SELECT
 Choose(1, 'First','Second','Third','Fourth','Fifth') [Static Index],
 Choose(@index, 'First','Second','Third','Fourth','Fifth') [Variable Index]

SQL CHOOSE function sample TSQL code


Choose() Function with Invalid Index

Of course it is possible to set the 1-based index value to zero (0) or to an index that is out of the range of the input array. In these cases the SQL Server Choose() function returns NULL as return value.

SELECT
 CHOOSE(0, 'T-SQL', 'PL/SQL', 'OSQL'),
 CHOOSE(999, 'A', 'B', 'C')

invalid index as SQL Server Choose() function argument

The index argument of the Choose() function can be NULL instead of a valid integer value. In such cases again, SQL Server function Choose() will return NULL value as output.

declare @i int = NULL
SELECT CHOOSE(@i, 'CHOOSE()', 'CONCAT()', 'IIF()')
SELECT CHOOSE(NULL, 'FIRST_VALUE()_', 'LAST_VALUE()', 'LEAD()', 'LAG()')

Choose() Function as an Expression to Concat() Function

Here is an other t-sql sample code where SQL Choose() function and Concat() function are used together in single SELECT statement. Note that the SQL Server Choose() function is used as an input expression argument to Concat() string concatenation function.

DECLARE @months int = 1
SELECT
 CONCAT('Last day of ', @months, ' months later is ',
 Choose(
  @months,
  eomonth(getdate(),1),
  eomonth(getdate(),2),
  eomonth(getdate(),3),
  eomonth(getdate(),4),
  eomonth(getdate(),5),
  eomonth(getdate(),6),
  eomonth(getdate(),7),
  eomonth(getdate(),8),
  eomonth(getdate(),9),
  eomonth(getdate(),10),
  eomonth(getdate(),11),
  eomonth(getdate(),12)
  )
 )

SQL Server CHOOSE function with EOMonth Function

I hope T-SQL developers are ready to use SQL CHOOSE() function in their codes. If you wonder other T-SQL functions introduced with SQL Server 2011, Denali CTP3 you can find a list of functions here New T-SQL Functions in SQL Server 2011 and here SQL Analytic Functions in SQL Server 2011







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems