How Did I Miss That? T-SQL CHOOSE()

I'm not sure how I missed this when it released in SQL Server 2012, but the CHOOSE function in T-SQL is going to change my life. Here's why.


The developer section of my brain loves Enums. They are succinct code structures that define sets of numeric values and let you reference them by name elsewhere in the code.

An example is event status in SentryOne:

	public enum RunStatus
		Nothing	= -1,
		Failed		= 0,
		Succeeded	= 1,
		Warning	= 8,
		Retry		= 2,
		Cancelled	= 3,
		[Description("In Progress")]
		InProgress	= 4,
		Unknown	= 5,
		Inactive	= 6,
		Waiting	= 7

This one code block defines the known statuses with a name to use later such as “RunStatus.Warning” which will translate to 8. It also defines—using attributes—how they should be sorted and labeled when viewed through an interface.

In some cases, like this one, when Enums are persisted, there isn’t always a way to tell what the numbers mean unless you can see the code. It gets documented in requirements or a wiki someplace, but you always find yourself looking it up every time, because you don’t access it enough to commit them all to memory.

This isn’t usually a big deal, but let’s say you have 20 engineers supporting 5,000 customer enterprises, and every time a user needs to know what these Enums translate to one of those engineers has to spend 45 minutes looking it up and crafting a response while the customer waits to continue what they were trying to accomplish. That time really adds up on both sides of the customer/support equation!


Now, there have been other ways to do this, which I’m not going to go into, but CHOOSE is an elegant solution to this particular problem.

Using the example of RunStatus, I can write statements like these:

SELECT CHOOSE(1,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(2,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(3,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(3,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(4,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(5,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(6,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(7,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(8,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;

And here's the output:

CHOOSE_output graphic

But you may be thinking, “Jason, that Enum has a 0 and -1 value. CHOOSE is going to break!” Very true. The following statements:

SELECT CHOOSE(-1,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;
SELECT CHOOSE(0,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') as RunStatus;

result in null values:

CHOOSE_null graphic

In this case, we do need to modify things a bit:

DECLARE @enumValue int = 0;

	CASE @enumValue
	WHEN -1 THEN 'Nothing'
	WHEN 0 THEN 'Failed'
	ELSE CHOOSE(@enumValue,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting','Warning') END
	AS RunStatus;

Which results, correctly, in:

CHOOSE_correct results graphic

Now we make this useful to our support engineers by driving it with actual data:

	RunStatus as RawRunStatus,
	CASE RunStatus
	WHEN -1 THEN 'Nothing'
	WHEN 0 THEN 'Failed'
	ELSE CHOOSE(Runstatus,'Succeeded','Retry','Cancelled','In Progress','Unknown','Inactive','Waiting', 'Warning') END
	AS RunStatus
FROM dbo.EventSourceHistory
WHERE RunStatus > -1; -- Filtering out the nothings

And the results (sampled, because I have a ton of events):

CHOOSE_sampled results graphic


I’ve covered what CHOOSE is, and how it helps in our world. You can see how several lines of conditional logic are condensed, and we can hang onto these scripts in our git repository to run later.

There are other use cases for CHOOSE—it’s great for creating buckets on date parts and other numeric values. I wouldn’t use it when you have categories past about 10 or so, but that is up to you. I just tend to get lost and prefer a lookup table at that point. I also haven’t tested performance relative to other methods, but I will save that for another time.

I hope you will find a good use for CHOOSE in your own projects!