Interesting bug in SQL 2016 CHOOSE

  • While trying, in SQL 2016 (13.0.5850.14), to see if CHOOSE allowed an inline query to provide the index value, I tried this:

    SELECT CHOOSE((SELECT 1), 'A', 'B', 'C') --<<-- WARNING! Generates a level 20 error (at least on my system)

    Of course this worked:

    SELECT CHOOSE(1, 'A', 'B', 'C") --<<-- works just fine, of course

     

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • No errors with SQL 2017 (14.0.3015.40)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Confirmed error in 2016 Enterprise and Developer Editions with latest CU.

    Msg 596, Level 21, State 1, Line 0

    Cannot continue the execution because the session is in the kill state.

    Msg 0, Level 20, State 0, Line 0

    A severe error occurred on the current command. The results, if any, should be discarded.

    Confirmed that 2017 Developer Edition with latest CU works as expected.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

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