The CHOOSE keyword

G’day,

I love coming across features of SQL that I’ve not used before and one happened today – the CHOOSE statement – introduced in SQL SERVER 2012.
So what does it do?
Well basically a label can be applied at a specific index in a list.
Here’s a demonstration script that uses two queries to achieve the same thing – the first way, using the CHOOSE method and the second way, using the CASE statement.

USE [tempdb];
GO

IF OBJECT_ID('tempdb..#Regions') IS NOT NULL
BEGIN
PRINT 'Dropping table #Regions';
DROP TABLE [#Regions];
END;
GO

CREATE TABLE [#Regions]
(
Number INT
);
GO

INSERT INTO [#Regions] VALUES (1);
INSERT INTO [#Regions] VALUES (2);
INSERT INTO [#Regions] VALUES (3);
INSERT INTO [#Regions] VALUES (4);
INSERT INTO [#Regions] VALUES (5);
GO

SELECT
[Number],
CHOOSE(Number , 'New Zealand' , 'Austrailia' , 'South Africa' , 'USA' , 'UK') [Country Name]
FROM
[#Regions]
ORDER BY
[Country Name]
GO

SELECT
[Number],
CASE ([Number])
WHEN 1 THEN 'New Zealand'
WHEN 2 THEN 'Australia'
WHEN 3 THEN 'South Africa'
WHEN 4 THEN 'USA'
WHEN 5 THEN 'UK'
ELSE 'UNKNOWN'
END [Country Name]
FROM
[#Regions]
ORDER BY
[Country Name];
GO

in case you’re wondering the query plans are identical

ChooseCase

 

 

 

have a great day.

Cheers

Martin,

Tags:

About Martin Catherall

Martin Catherall is a SQL Server MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Martin Catherall is an SQL SERVER Database Administrator in the Christchurch Area and has been active in the local IT community for over 10 years. He likes learning interesting stuff about databases and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.