Using the TSQL CHOOSE Function

Here is a short video tutorial that shows how to use the CHOOSE function in T-SQL on SQL Server 2012, SQL Server 2014 or Newer. This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the sample code that goes along with the CHOOSE function training. This sample code has been tested and confirmed working on SQL Server 2012 and SQL Server 2014.

 

CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
GO
------------------------------------------------
-- CHOOSE (2012, 2014 or newer)
-- returns the item at a specific index

declare @corners as int = 6;
-- the old way using case.
SELECT CASE @corners
		WHEN 1 THEN 'point'
		WHEN 2 THEN 'line'
		WHEN 3 THEN 'triangle'
		WHEN 4 THEN 'square'
		WHEN 5 THEN 'pentagon'
		WHEN 6 THEN 'hexagon'
		WHEN 7 THEN 'heptagon'
		WHEN 8 THEN 'octagon'
		ELSE NULL
	   END;

-- now with choose
declare @corners as int = 6;
SELECT choose(@corners, 'point', 'line', 'triangle', 'square', 'pentagon',
                        'hexagon', 'heptagon', 'octagon')

-- CHOOSE day of week example
DECLARE @day as int=4
SELECT CHOOSE(@day,'Sunday','Monday', 'Tuesday', 'Wednesday',
                   'Thursday', 'Friday', 'Saturday');

-- Pick 5 people at random from a list of 10
GO
DECLARE @myRandonNum INTEGER;
SET @myRandonNum = CAST(RAND() * 10 AS INTEGER) + 1;
SELECT @myRandonNum as Num, CHOOSE(@myRandonNum, 'Steve', 'Aaron', 'Pete', 'Cody',
                     'Gabe', 'Bill', 'Aaron', 'Jordan', 'Darren', 'Mark', 'Aaron') as Name;
GO 5

Notice on the last line of the sample, the GO command has the number 5 right after it. This tells SSMS to run the batch 5 times. See my blog post on GO for more details.

More Info:

SQL Sunday Fun – Word Search with New T-SQL 2012 Functionality

 

 

new_sql2012_word_jumbleClick on the grid to enlarge, then print it.

Find the following words in the grid up, down, left, right, horizontal, vertical, and diagonal.

ROWS  RANGE  PRECEDING 
FOLLOWING  UNBOUNDED  IIF 
CHOOSE  OFFSET  FETCH 
FORMAT  CONCAT  SEQUENCE 
PARSE  TRYPARSE  TRYCONVERT 
THROW  LEAD  LAG 
FIRSTVALUE  LASTVALUE  PERCENTRANK 
CUMEDIST  PERCENTILEDISC  PERCENTILECONT 

NOTE:  some of these terms normally have an underscore in them, but for the purpose of the word scramble the underscores have been omitted.

 

Related Links:

SEQUENCE object

CHOOSE function

Analytics – Analytic Functions – PERCENT_RANK

Analytics PERCENTILE_DISC and PERCENTILE_CONT

IIF function in TSQL.

TSQL Analytic Functions LEAD and LAG

OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

OFFSET / FETCH in a SELECT statement

Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

 

 

SQL Saturday Presentation

I am at SQL Saturday in Vancouver BC today.

Here is the download of my presentation.

Download zip file.

Here is the presentation outline.

  • OVER Clause Enhancements
  • ROWS PRECEDING, FOLLOWING, UNBOUNDED
  • RANGE PRECEDING, FOLLOWING, UNBOUNDED
  • IIF – Immediate IF or Inline IF (from Access)
  • CHOOSE (from Access)
  • OFFSET / FETCH
  • FORMAT
  • CONCAT
  • SEQUENCE (from Oracle)
  • sp_describe_first_result_set
  • New Date and Time Functions
  • Conversion Functions
  • PARSE, TRY_PARSE, TRY_CONVERT
  • THROW exception

TSQL 2012 CHOOSE Function

In SQL Server 2012 there is a new function called CHOOSE that takes in an offset, and a list of options.  Choose is a function that returns the item at a specific index.

Syntax:
—CHOOSE(index, val_1, val_2, val_3, …)
—If the index is greater than the number of values or less than 1 it returns NULL
—Easier than using a CASE statement in some examples.
Example:
</div>
<div>
-- CHOOSE
-- returns the item at a specific index
declare @corners as int = 6
SELECT choose(@corners, 'point', 'line', 'triangle', 'square',
 'pentagon', 'hexagon', 'heptagon', 'octagon')

-- the old way using case.
SELECT CASE @corners
 WHEN 1 THEN 'point'
 WHEN 2 THEN 'line'
 WHEN 3 THEN 'triangle'
 WHEN 4 THEN 'square'
 WHEN 5 THEN 'pentagon'
 WHEN 6 THEN 'hexagon'
 WHEN 7 THEN 'heptagon'
 WHEN 8 THEN 'octagon'
 else NULL
 END;

In the above example, the CASE statement and CHOOSE function do the same thing, but the CHOOSE function is a bit easier to write.Here is another example

-- CHOOSE day of week example
DECLARE @day as int=4
SELECT CHOOSE(@day,'Sunday','Monday', 'Tuesday',
              'Wednesday','Thursday','Friday','Saturday')
For choose and other new TSQL functionality in Server 2012 take a look at my What’s New in TSQL 2012 presentation, which will be presented at Seattle Code Camp on June 16th.