Oleg Netchaev (4/13/2010)

---

This is a great question, I really, really enjoyed it. The most difficult part was to figure out that the line:

set @result = cast (@@rowcount as varchar)

does 2 things:

1. it sets the value of the @result variable to 1 because @@rowcount was equal to 1 as a result of the last insert
2. it resets @@rowcount to 1 as a result of @result assignment.

The @@rowcount then stays unchanged (still equal to 1) because the

select @result = @result + cast (@@rowcount as varchar) from #n; 

which pads the @result has the @@rowcount inside of it, but the moment the statement bails out the @@rowcount is then reset to whatever the number of affected records was, which happens to be 3.

The commented line reading set nocount off; was there just for demonstration purposes I suppose, meaning that it could be any other set statement, such as set ansi_nulls on; or set transaction isolation level read uncommitted; or whatever other set. Any such set statement when uncommented would just reset the @@rowcount to 0 changing the final answer to 10003 from 11113.

I really hope that what I figured is correct, and my answer was not a result of some lucky guess.

Thanks again.

Oleg

honza.mf (4/14/2010)

---

Sorry, ronmoses, for your headache.

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.

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

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.


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.