Rune Bivrin (4/14/2010)
It's important to note that SET @result = cast (@@rowcount as varchar) does NOT change @@ROWCOUNT. The first 1 comes from the last INSERT #n VALUES(3).This is one of the important differences between SELECT and SET when assigning variables. SET never yields a rowcount, and thus doesn't change @@ROWCOUNT.
Are you sure about this? I tried the following on my server and did not get a result consistent with your statement above. I am using SQL 2005
declare @result int
select 1 union select 2
set @result = @@rowcount
select @@rowcount
I got
-----------
1
2
-----------
1