Tom Garth (4/14/2010)

---

That was fun and surprising. I didn't notice the quirky update until I ran it.

Thanks for a good QotD.

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.

That does not seem to be true. @@ROWCOUNT yields the result of the last command. SET yields 1 whereas PRINT yields 0.

declare @n varchar(5)

select @n = @@rowcount

/*Row count is 1 after SELECT*/

print @@rowcount

/*Row count is 0 after PRINT*/

print @@rowcount

set @n = @@rowcount

/*Row count is 1 after SET*/

print @@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'.

Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

EXECUTE statements preserve the previous @@ROWCOUNT.

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.