Variables, Nulls and Coalesce

Question

Variables, Nulls and Coalesce

Kevin Gill

SSCrazy

Points: 2348
More actions
November 24, 2009 at 10:05 pm

#228169

Comments posted to this topic are about the item Variables, Nulls and Coalesce
-------------------------------Oh no!

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

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

cengland0 SSCertifiable Points: 6102 More actions · Answer 1

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.

honza.mf SSCertifiable Points: 5519 More actions · Answer 2

: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

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 3

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]

cengland0 SSCertifiable Points: 6102 More actions · Answer 4

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.

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 5

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!

Kevin Gill SSCrazy Points: 2348 More actions · Answer 6

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!

Tom Garth SSCertifiable Points: 6193 More actions · Answer 7

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

gary.rumble SSCommitted Points: 1903 More actions · Answer 8

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

honza.mf SSCertifiable Points: 5519 More actions · Answer 9

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:

Tom Garth SSCertifiable Points: 6193 More actions · Answer 10

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

dun Say Hey Kid Points: 708 More actions · Answer 11

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

Kevin Gill SSCrazy Points: 2348 More actions · Answer 12

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!

Rob Goddard SSCrazy Points: 2622 More actions · Answer 13

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]

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 14

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.