Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

T-SQL 2012 #2 Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 5:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:26 AM
Points: 1,803, Visits: 2,168
Thanks for a Monday question, that still made me learn something.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1406660
Posted Monday, January 14, 2013 6:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:58 PM
Points: 543, Visits: 574
Good introduction the CHOOSE function.
Post #1406689
Posted Monday, January 14, 2013 7:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:56 AM
Points: 1,860, Visits: 1,398
Great question about a new feature.



Everything is awesome!
Post #1406716
Posted Monday, January 14, 2013 7:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
What would be a good use of this function?
Why wouldn't you just add a lookup table containing the index and description, and join to that?
So that any changes required to a description could be made by just updating the lookup table, rather than rewriting SQL.
Post #1406723
Posted Monday, January 14, 2013 7:46 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks for the question.
Post #1406737
Posted Monday, January 14, 2013 8:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
Koen Verbeeck (1/14/2013)
Great question about a new 2012 feature. Thanks Ron!


I must concur... that's a neat function that I had missed. I got this one right because there was only one answer that made sense, returning an integer after being given a bunch of comma separated strings wouldn't make sense to me, but I didn't know about choose until now. :)




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1406753
Posted Monday, January 14, 2013 8:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:44 AM
Points: 1,410, Visits: 253
Thanks, for the reminder about the Choose function. The beauty is the ability to provide two things, an alternate name or identification as well as a different sort order than if the value provided was the content of the field. Of course this really only works well if there is a short list of values to translate.
Post #1406765
Posted Monday, January 14, 2013 8:36 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Toreador (1/14/2013)
What would be a good use of this function?
Why wouldn't you just add a lookup table containing the index and description, and join to that?
So that any changes required to a description could be made by just updating the lookup table, rather than rewriting SQL.


This was exactly what I was wondering.
Post #1406769
Posted Monday, January 14, 2013 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
SQLRNNR (1/12/2013)
Glad we have this feature added.


But are the CHOOSE function and its also-new-to-SQL-Server-2012 buddy IIF anything other than syntactical sugar for Visual Basic coders writing T-SQL? The CHOOSE function is just a less-flexible shorthand for the ANSI-standard CASE, and it can only be used when evaluating an equality condition for an integer value (the index argument), although I concede that CHOOSE requires less typing than CASE in these limited circumstances. Similarly, the IIF function saves a few keystrokes when there are only two possible values to return, but once you start nesting IIF statements, the code becomes more difficult to understand than the equivalent CASE expression, in my opinion.

SQL Server 2012 just converts CHOOSE and IIF function calls to CASE expressions anyway. I have a code sample that shows that the CHOOSE and IIF functions produce Compute Scalar operators that have Defined Values that are identical to the one produced by the equivalent CASE expression, but I'm having some trouble posting it. I'll try again later.

Edit: I have posted a screenshot of the code, a .txt file of the code, and a .sqlplan file of the execution plan that I get on my SQL Server 2012 instance, if you're interested. Still not sure why I can't post code - I get an error screen that suggests that my employer is blocking the outgoing traffic when it includes certain kinds of code snippets - hmmm . . .


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth


  Post Attachments 
CHOOSEvsIIFvsCASE.txt (4 views, 1.00 KB)
CHOOSEvsIIFvsCASE.jpg (3 views, 80.22 KB)
CHOOSEvsIIFvsCASE.sqlplan (1 view, 82.33 KB)
Post #1406792
Posted Monday, January 14, 2013 9:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
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
BoL
Returns the data type with the highest precedence from the set of types passed to the 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.



I read this and changed my mind to integer.
Ah well, another point gone begging.
Post #1406808
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse