rjv_rnjn (4/14/2010)

---

Well, I thought I'd @@ROWCOUNT in the bag until this came along
Nice question.

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.

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

declare @result varchar (5)
create table #n (n int)
insert #n values (1),(2),(2)
set @result = cast (@@rowcount as varchar)
/*SET NOCOUNT OFF*/
select @result = @result + cast (@@rowcount as varchar) from #n
select @result + cast (@@rowcount as varchar)
drop table #n

Tom.Thomson (4/17/2010)

---

Someone may already have said this, but in case they haven't:

Paul White NZ (4/18/2010)

---

Actually, pretty much every other post so far has covered it and Rune responded:
"Yep, I was wrong about that. And I even ran a test to verify my thought before I posted, but I managed to read the results of that test wrong. My bad. I suppose that was because I expected it to work that way simply based on the fact that SET doesn't yield a 1 row(s) affected." Ah, well. Learn something new everyday.