Get @@ROWCOUNT @ @ERROR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get @@ROWCOUNT @ @ERROR

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-04-21 : 14:25:25

In a SPROC I want to get both @@ROWCOUNT and @@ERROR into variables after an update or insert statement. It seems that once one is set the other is cleared. How can I save them both?

Declare @ERVal int, @RCVal int

UPDATE MyTable Set MyField = 'Ken'

SET @ERVal = @@ERROR
SET @RCVal = @@ROWCOUNT

RCVal result is 1, even though 21 records are affected. This is due to the previous SET @ERVal command. If I swap them, then I don't get the error if there was one on the UPDATE command because the SET @RCVal command would reset @@ERROR to 0.

The ending result is I would like to report both the error code and rowcount when/if there is an error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 14:26:06

SELECT @ERVal = @@ERROR, @RCVal = @@ROWCOUNT

So you can't do it with SET, but you can with SELECT.

Tara