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.

Enums

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
	{
        [SortKey(0)]
		[Description("Nothing")]
		Nothing	= -1,
         [SortKey(4)]
		[Description("Failed")]
		Failed		= 0,
         [SortKey(8)]
		[Description("Succeeded")]
		Succeeded	= 1,
         [SortKey(12)]
		[Description("Warning")]
		Warning	= 8,
         [SortKey(16)]
		[Description("Retry")]
		Retry		= 2,
         [SortKey(20)]
		[Description("Cancelled")]
		Cancelled	= 3,
         [SortKey(24)]
		[Description("In Progress")]
		InProgress	= 4,
         [SortKey(28)]
		[Description("Unknown")]
		Unknown	= 5,
         [SortKey(32)]
		[Description("Inactive")]
		Inactive	= 6,
         [SortKey(36)]
		[Description("Waiting")]
		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!

T-SQL CHOOSE()

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:

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:

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

DECLARE @enumValue int = 0;

SELECT 
	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:

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

SELECT
	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):

Conclusion

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!

Jason Hall

Jason has worked in technology for over 20 years. He joined SentryOne in 2006 having held positions in network administration, database administration, and software engineering. During his tenure at SentryOne, Jason has served as senior software developer and founded both Client Services and Product Management. His diverse background with relevant technologies made him the perfect choice to build out both of these functions.

As SentryOne experienced explosive growth, Jason returned to lead SentryOne Client Services, where he ensures that SentryOne customers receive the best possible end to end experience in the ever-changing world of database performance and productivity.