L' Eomot Inversé (1/12/2013)

---

Nice clear straightforward question.
Good to see a question on this new feature.

Interestingly, there's an error on the BoL page: it says the CHOOSE function
The first argument is an integer, which has a higher type precedence than any character type, but if it really was going to return an int we would have string to int conversion errors here. BoL should instead say that the return type is highest precedence type of the arguments other than the first.

Koen Verbeeck (1/14/2013)

---

Great question about a new 2012 feature. Thanks Ron!

(and thanks for including me )

KWymore (1/14/2013)

---

Thanks for the 2012 question. I'm not sure why I would ever use CHOOSE. I would usually join to a reference table, or use a case statement, or create a temp table if it was a small set of values to type up for an ad-hoc query or one off report. This does seem more geared towards .NET developers. I think if I came across it in production I would have to turn the array into a reference table.

L' Eomot Inversé (1/15/2013)

---

If you would sometimesd use a CASE expression for this, why object to CHOOSE? It's just a simplified syntax for CASE in a particular case. Presumably it does index into the list, so it will have better performance than case when it is applicable (because the optimiser surely isn't going to look and see if the set of values for a simple case statement provides an ungapped sequence).

wolfkillj (1/15/2013)

---

Hi Tom,

Check my post above (about a page back in this thread) - the optimizer produces an execution plan with a Compute Scalar operator for CHOOSE with a defined value that is identical to that of CASE. It looks like the T-SQL implementation of CHOOSE truly is nothing but an alternative syntax for CASE when writing a conditional expression based on an equality comparison of integer values. SQL Server treats them exactly the same way.

Jason

Jason