Variables, Nulls and Coalesce

  • Comments posted to this topic are about the item Variables, Nulls and Coalesce

    -------------------------------Oh no!

  • I almost selected the option that said there would be an error on the first select because the query should return more than one value.

    select @a = iValue from @vals where iKey = iValue order by iKey

    After thinking about it and before submitting my answer, I realized it would work but pick the last item and assign it to @a.

    This one almost got me.

  • :w00t:

    I know very well how this assignments work. So it was just my great answering problem: Don't miss the right radio button. 😀



    See, understand, learn, try, use efficient
    © Dr.Plch

  • WARN: If you use an aggregate function @a will be updated also if no row is returned

    [font="Arial"]SET NOCOUNT ON

    declare @vals table (iKey int, iValue int)

    insert into @vals (iKey, iValue)

    select 1,1

    union select 2,2

    union select 3,3

    declare @a int

    set @a = 5

    select @a = iValue from @vals where iKey = iValue order by iKey

    select @a

    select @a = iValue from @vals where iKey <> iValue

    select @a

    select @a = COUNT(*) from @vals where iKey <> iValue

    select @a[/font]

  • Carlo Romagnano (11/25/2009)


    WARN: If you use an aggregate function @a will be updated also if no row is returned

    [font="Arial"]SET NOCOUNT ON

    declare @vals table (iKey int, iValue int)

    insert into @vals (iKey, iValue)

    select 1,1

    union select 2,2

    union select 3,3

    declare @a int

    set @a = 5

    select @a = iValue from @vals where iKey = iValue order by iKey

    select @a

    select @a = iValue from @vals where iKey <> iValue

    select @a

    select @a = COUNT(*) from @vals where iKey <> iValue

    select @a[/font]

    Wouldn't you always get a row returned with the COUNT(*) aggregate? It might return 0 as the result but that still returns a row.

  • Wouldn't you always get a row returned with the COUNT(*) aggregate? It might return 0 as the result but that still returns a row.

    That's true!

  • cengland0 (11/25/2009)

    Wouldn't you always get a row returned with the COUNT(*) aggregate? It might return 0 as the result but that still returns a row.

    Indeed - if you slot an aggregate that returns NULL into the query around the 'iValue' in the final COALESCE, you actually get zero, as the MAX aggregate (to take an example at random) actually returns a row with NULL in.

    Interesting observation. 🙂

    -- Kev

    -------------------------------Oh no!

  • The 2nd SELECT has no ORDER BY clause, and the table variable has no index. How do you know what the last row will contain?

    😛

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Tom Garth (11/25/2009)


    The 2nd SELECT has no ORDER BY clause, and the table variable has no index. How do you know what the last row will contain?

    😛

    The second query returns null so the variable isn't modified from the first query.

    14090 SW TENNESSEE LN

  • gary.rumble (11/25/2009)


    Tom Garth (11/25/2009)


    The 2nd SELECT has no ORDER BY clause, and the table variable has no index. How do you know what the last row will contain?

    😛

    The second query returns null so the variable isn't modified from the first query.

    I hope it was a joke (see emoticon)

    :w00t:



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Ya got me.

    :crying:

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.

  • dun (11/26/2009)


    This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.

    Glad you enjoyed it 🙂 This example actually came to mind because of the amount of times I fell over things like this when I was doing serious T-SQL programming for the first time a couple of years ago, when trying to improve and modify the thousands of lines of code left for us to ... try to deal with ... by an (SEI Level 5 - ha!) outsourcing company who wrote our then brand-new system.

    -- Kev

    -------------------------------Oh no!

  • Nice question.

    Along the same lines of "no rows returned, do nothing", I have a feeling that it may be a case of "400 rows returned, assign 400 values to the same variable" so you are just left with the last one it assigned.

    My question is, if you're not doing anything with that variable (i.e. "select @val = col from tab" rather than "select @val = @val + col from tab"), does the optimizer spot this and skip all the variable assignments and just do the last one, or are you inadvertantly creating overhead by assigning every single value that you will never see?

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • Rob Goddard (11/27/2009)


    does the optimizer spot this and skip all the variable assignments and just do the last one?

    All the variable assignments are done.

Viewing 15 posts - 1 through 15 (of 19 total)

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