March 21, 2015 Leave a Comment
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.