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 an SQL SERVER DBA working in Christchurch on the south island of New Zealand. 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 young son Callum Glen.