We recently published an article on CHOOSE at SQL Server Central. I thought it was a good intro, but as someone noted in the comments, how do you use CHOOSE? Do you have to hard code choices?

This post shows you don’t.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A Scenario

I have a table that contains some data. In this case, about beer. I like beer, and this was a fun little demo. I’m not recreating the DDL because, well, you might like different beers.

In any case, this is simple to set up.

If I wanted to choose some data from this table based on an index, I could do something like this. This code populates the first index in choose with beers and the second with brewers. CHOOSE is 1-based indexing.

DECLARE @i INT = 1;
SELECT
   CHOOSE (@i, beername, brewer)
FROM dbo.Beer AS b2;

This returns me the beers.

If I changed the value to 2, I get brewers. I show both below.

How would I use this? Maybe a user is asking to edit either a home or shipping address. I can index these by returning the column data as index 1 or 2, and linking the user suggestion to the index. They choose home, we pass in 1. If we qualify the query with a WHERE clause to one customer, they get just their data to edit.

I could even do something silly, like getting values from different places. For example, here I’ll use string_split on a value.

DECLARE @i INT = 2;
DECLARE @s VARCHAR(20) = 'Vodka,Tequila,Bourbon'
; WITH a (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 1
),
  b (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 2
),
  c (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 3
) 
SELECT
   CHOOSE (@i, a.value, b.value, c.value)
  FROM a, b, c

This is silly, but it does return an acceptable answer.

I don’t know that there are many places that I’d use CHOOSE, but as I play with it, I can see that it could be a handy tool at times with a little creativity.

SQL New Blogger

This post took me about 15 minutes to write after I saw a comment. I set up a scenario and posted a reply, then took that code to structure this post. The STRING_SPLIT piece was the longest, as I had to futz with code, but I show some use of a new feature and how I might incorporate this into an application.

You could write your own creative blog on this, probably in 30 minutes or less. I bet you’d get asked about this in an interview as it’s kind of funny.