[thelist] SQL Server & Stored Procedures

Paul Cowan paul at wishlist.com.au
Tue Oct 30 17:06:37 CST 2001


Hi Scott, and all,

> You could check out writing a trigger that'd fire on the delete ... I'm
> not too hip on the @@rowcount property because it is a server global,
> and we've (here at work) have seen it be not so reliable in high traffic
> (it will be the value of another operation somewhere else in the db
> server, not of the statement you just fired) 

I'd like to take issue with this... are you SURE? This doesn't sound right,
and when I checked with our DBA he actually snorted. :)

If you HAVE observed this behaviour, then I would suggest it's a bug in your
version of SQL, because that is incorrect behaviour... @@rowcount should be
a per-connection value.

Just checking....

... hmmm ....

... SQL Server books online has nothing. Trying Inside SQL Server 7.0 from
Microsoft.. EXCELLENT book by the way. Aaah, here we go.

	"These functions are global only in the sense that any 
	 connection can access their values. However, in many cases 
	 the value returned by these functions is specific to the 
	 connection. For example, @@ERROR represents the last error 
	 number generated for a specific connection, not the last error 
	 number in the entire system. @@ROWCOUNT represents the number 
	 of rows selected or affected by the last statement for the 
	 current connection. "

Either the behaviour you've observed is a bug, or the @@rowcount you're
looking at has been affected by some other intermediate event - like a
trigger on the table you're updating, perhaps? I'm just making this up.

I have no idea if 
	UPDATE Author SET Surname = 'Smith'
	PRINT @@ROWCOUNT
where a trigger on Author inserted rows into ANOTHER table, would print the
# of rows affected in 'Author', or in the table changed by the trigger... 

So yeah, be careful, but I believe that what you say above is not correct.

Paul




More information about the thelist mailing list