The SQL Server CHOOSE function is a simple decision structure you can use to simplify an otherwise complex expression.
The syntax for CHOOSE is extremely simple:
CHOOSE(<integer>, <expression1>, <expression2>,......<expressionN>)
All you do it outline an integer as the first parameter, followed by a comma-separated list of expressions. The expressions can be simple string literals or whatever else you need them to be.
As stated, the first parameter is an integer, which we’ll call N. The function will return the Nth expression from the list of expressions outlined.
Here’s a really silly example:
We outlined an N value of 5, which means the 5th expression is returned. If we change the integer to 4, the 4th expression is returned:
Very easy.
A more complex example:
Take a look at the following query:
This query shows us some simple details about orders that have been placed for our business.
Notice the Material column. We are in the business of making wood furniture, and internally, we work with three species of wood:
- Oak
- Walnut
- Cherry
The Material column matches up to the type of wood the customer wants their product constructed from.
- 1 is for Oak
- 2 is for Walnut
- 3 is for Cherry
Wouldn’t it be nice to actually see those wood species in our query instead of those unhelpful numbers? Using the CASE expression, we can do that relatively easily:
But we can use CHOOSE in this scenario as a shorthand way of writing that CASE expression:
So much easier!
Next Steps:
Leave a comment if you found this quick tutorial helpful!
Of course, the CHOOSE function is meant to be a substitute for a very simple CASE expression. If you need to write something more complex, you’ll want to just use the standard CASE expression. Make sure you check out the full tutorial on the CASE expression to understand how it works:
SQL Server CASE Statement: A How-To Guide
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
