Using the TSQL CHOOSE Function

Download PDF

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];
USE [QueryTraining];
-- 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'

-- 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
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:

Tagged with: , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *


Time limit is exhausted. Please reload CAPTCHA.