## Variables, Nulls and Coalesce

 Author Message Kevin Gill SSCommitted Group: General Forum Members Points: 1671 Visits: 356 Comments posted to this topic are about the item Variables, Nulls and Coalesce -------------------------------Oh no! cengland0 Hall of Fame Group: General Forum Members Points: 3314 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 iKeyAfter 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 Hall of Fame Group: General Forum Members Points: 3593 Visits: 1323 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 SSChampion Group: General Forum Members Points: 12362 Visits: 3517 WARN: If you use an aggregate function @a will be updated also if no row is returned SET NOCOUNT ONdeclare @vals table (iKey int, iValue int)insert into @vals (iKey, iValue)select 1,1union select 2,2union select 3,3declare @a intset @a = 5select @a = iValue from @vals where iKey = iValue order by iKeyselect @aselect @a = iValue from @vals where iKey <> iValueselect @aselect @a = COUNT(*) from @vals where iKey <> iValueselect @a cengland0 Hall of Fame Group: General Forum Members Points: 3314 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 ONdeclare @vals table (iKey int, iValue int)insert into @vals (iKey, iValue)select 1,1union select 2,2union select 3,3declare @a intset @a = 5select @a = iValue from @vals where iKey = iValue order by iKeyselect @aselect @a = iValue from @vals where iKey <> iValueselect @aselect @a = COUNT(*) from @vals where iKey <> iValueselect @aWouldn'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 SSChampion Group: General Forum Members Points: 12362 Visits: 3517 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 SSCommitted Group: General Forum Members Points: 1671 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 Hall of Fame Group: General Forum Members Points: 3483 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 GarthVertical 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 Ten Centuries Group: General Forum Members Points: 1326 Visits: 1715 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?:-PThe second query returns null so the variable isn't modified from the first query. honza.mf Hall of Fame Group: General Forum Members Points: 3593 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?:-PThe 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