A Simple Choice

Question

A Simple Choice

Steve Jones - SSC Editor

SSC Guru

Points: 737236
More actions
February 19, 2025 at 12:00 am

#4502579

Comments posted to this topic are about the item A Simple Choice

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

edwardwill SSCertifiable Points: 5902 More actions · Answer 1

Assuming that your table contained only 1 row as specified, I don't think the answer is correct. I spun up a nonce table with exactly that data and the query returned three rows

(No column name)

NULL

Laptops

PCs

Tonie Huizer SSC-Addicted Points: 461 More actions · Answer 2

The CHOOSE function will select the 3rd (CatID value of the only row in dbo.Categories) value of the rest of the CHOOSE function parameters. But there is no 3rd value, hence the answer is NULL

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-choose-transact-sql?view=sql-server-ver16

You try it out with this script, or online via this link (don't forget to click connect).

CREATE TABLE Categories (catId int, CatName Varchar(20))

Insert into Categories (Catid, CatName)
values (3, 'Monitors')

SELECT CHOOSE(catid, 'Laptops', 'PCs') FROM dbo.Categories AS c

--clean up part
DROP TABLE Categories

Cheers, Tonie

See you at Sea(QL) this year?
https://seaql.nl

Jeffrey Williams SSC Guru Points: 90341 More actions · Answer 3

CHOOSE is really just short-hand for a simple case expression:

CASE CatID
     WHEN 1 THEN 'Laptops'
     WHEN 2 THEN 'PCs'
 END

Since there is no match to the value 3 - it returns NULL the same as would be returned by the simple case expression. You can even verify this is how SQL Server interprets CHOOSE by showing the execution plan and viewing the properties on the select operation. You will see that the code generated is a simple CASE expression.

With that said - it is only useful when you have a value that increments by 1 and is part of a limited set. If you have varying values and require a default non-null value then the CASE expression would be a better choice. For example, if you have statuses where the status is denoted be 1, 2 or 3 - where 1 = Active, 2 = Inactive and 3 = Suspended, then CHOOSE(Status, 'Active', 'Inactive', 'Suspended') is much shorter than the CASE expression.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

edwardwill SSCertifiable Points: 5902 More actions · Answer 4

Thank you to Tonie and Jeffrey for explaining this to me ... I was completely wrong!

Tonie Huizer SSC-Addicted Points: 461 More actions · Answer 5

No problem Edward!

And Jeffrey thanks for pointing out to look at the Execution plan.

I knew CHOOSE would become a CASE, but I never knew it was that visible!

Cheers, Tonie

See you at Sea(QL) this year?
https://seaql.nl