SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Variables, Nulls and Coalesce


Variables, Nulls and Coalesce

Author
Message
Kevin Gill
Kevin Gill
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1181 Visits: 356
Comments posted to this topic are about the item Variables, Nulls and Coalesce

-------------------------------
Oh no!
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 1300
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
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1947 Visits: 1323
w00t
I know very well how this assignments work. So it was just my great answering problem: Don't miss the right radio button. :-D



See, understand, learn, try, use efficient
© Dr.Plch
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4990 Visits: 3326
WARN: If you use an aggregate function @a will be updated also if no row is returned
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


I run on tuttopodismo
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 1300
Carlo Romagnano (11/25/2009)
WARN: If you use an aggregate function @a will be updated also if no row is returned
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


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
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4990 Visits: 3326

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!

I run on tuttopodismo
Kevin Gill
Kevin Gill
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1181 Visits: 356
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
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 1499
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?

:-P

Tom Garth
Vertical Solutions

"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
gary.rumble
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1631
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?

:-P


The second query returns null so the variable isn't modified from the first query.
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1947 Visits: 1323
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?

:-P


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search