November 24, 2009 at 10:05 pm
Comments posted to this topic are about the item Variables, Nulls and Coalesce
-------------------------------Oh no!
November 25, 2009 at 3:42 am
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.
November 25, 2009 at 4:22 am
:w00t:
I know very well how this assignments work. So it was just my great answering problem: Don't miss the right radio button. 😀
November 25, 2009 at 6:24 am
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]
November 25, 2009 at 6:34 am
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.
November 25, 2009 at 6:38 am
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!
November 25, 2009 at 10:07 am
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!
November 25, 2009 at 1:35 pm
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?
😛
November 25, 2009 at 1:45 pm
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
November 25, 2009 at 1:51 pm
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:
November 25, 2009 at 1:54 pm
November 26, 2009 at 7:26 am
This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.
November 26, 2009 at 7:30 am
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!
November 27, 2009 at 6:52 am
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]
November 27, 2009 at 7:00 am
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