Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Variables, Nulls and Coalesce Expand / Collapse
Author
Message
Posted Tuesday, November 24, 2009 10:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:02 AM
Points: 1,105, Visits: 298
Comments posted to this topic are about the item Variables, Nulls and Coalesce

-------------------------------
Oh no!
Post #824296
Posted Wednesday, November 25, 2009 3:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #824399
Posted Wednesday, November 25, 2009 4:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:20 AM
Points: 1,339, Visits: 1,312

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
Post #824416
Posted Wednesday, November 25, 2009 6:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342
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
Post #824462
Posted Wednesday, November 25, 2009 6:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #824478
Posted Wednesday, November 25, 2009 6:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342

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!
Post #824486
Posted Wednesday, November 25, 2009 10:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:02 AM
Points: 1,105, Visits: 298
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!
Post #824762
Posted Wednesday, November 25, 2009 1:35 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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

"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
Post #824915
Posted Wednesday, November 25, 2009 1:45 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:58 AM
Points: 810, Visits: 1,269
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.
Post #824922
Posted Wednesday, November 25, 2009 1:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:20 AM
Points: 1,339, Visits: 1,312
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)




See, understand, learn, try, use efficient
© Dr.Plch
Post #824927
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse