# Variables, Nulls and Coalesce

• Kevin Gill

SSCrazy

Points: 2316

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

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

• cengland0

SSCertifiable

Points: 6102

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

: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

• Carlo Romagnano

SSC-Insane

Points: 21943

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

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: 21943

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: 2316

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: 6173

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: 1763

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.

• honza.mf

SSCertifiable

Points: 5519

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

• Tom Garth

SSCertifiable

Points: 6173

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

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

• Kevin Gill

SSCrazy

Points: 2316

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: 2572

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: 21943

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 20 total)