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.

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.

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?

gary.rumble (11/25/2009)

---

The second query returns null so the variable isn't modified from the first query.