More Choice

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718065

    Comments posted to this topic are about the item More Choice

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71680

    Nice, easy one to start the day on, thanks Steve

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • NBSteve

    Hall of Fame

    Points: 3363

    The data type of the values is not important here, as they can vary. The value of @j will determine the casting. If the data cannot be implicitly cast to the data type, an error is returned.

    This is, at best, a misleading explanation.  From BOL:

    Returns the data type with the highest precedence from the set of types passed to the function.

    In the example above, the highest precedence in the list is decimal/numeric, and so no matter what value @j is, the value will first be cast to decimal/numeric.  It may then be cast a second time when assigned to @j, depending on @j's data type, but the first casting can cause an error even if @j's type matches the expression.

    Consider this variation on the question:

    DECLARE @i INT = 6, @j varchar(10)
    SELECT @j = CHOOSE(@i, 10,20,30,3.14,50,'Test')
    SELECT @j

    On the surface, we're assigning 'Test' to a varchar(10) variable.  However, this fails because prior to assigning 'Test' to @j, it must be converted to numeric in order to evaluate the CHOOSE expression.  It is true that any data types can be in a CHOOSE expression, and interesting to note that invalid conversions are not attempted except for the expression at the specified index.  But it is not true that the other data types aren't important.

  • Kathi Kellenberger

    Old Hand

    Points: 361

    I modified the explanation a bit. The answer stands since he's asking about the value returned by that batch which runs successfully.

  • shams.hedayti 19669


    Points: 11


    1 - Index used to begin with (0) meaning index of (6) = null

    2- Even if index begins with (1) the index of (6) = 'test' which would cause an error since 'test' could not be an integer


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

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