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

have a great day.

Cheers

Martin,