Patrick Foley’s Blog

Bottom line: when you need to save both @@ERROR and @@ROWCOUNT, be sure to set them in one line:

select @MyRowCount = @@ROWCOUNT, @MyError = @@ERROR

More detail: When programming SQL Server stored procedures with Transact-SQL, you should check @@ERROR after every database call.

select mycolumn from mytable
if @@ERROR <> 0
begin
…
end


There are many built-in variables in SQL Server - look in the help under @@. Values for variables like @@ERROR are only useful until the next call - database or not. That means you often need to save the @@ variable to your own, so you can check it a bit later.

select mycolumn from mytable
SET @MyError = @@ERROR

It’s frequently handy to know how many rows were affected (returned by a select, modified by an updated, etc.) by the last database call as well. For that, use @@ROWCOUNT

A common mistake is to set the values separately:

select mycolumn from mytable
SET @MyError = @@ERROR
SET @MyRowCount = @@ROWCOUNT

This will result in @MyRowCount always being 1 - the SET @MyError line affects @@ROWCOUNT.

The correct way to work with these values is to store them to local variables in one line, using select:

select mycolumn from mytable
select @MyRowCount = @@ROWCOUNT, @MyError = @@ERROR

That safely saves both values in one shot. There’s something vaguely irritating about it, but that’s just the way it works.

This entry was posted on Friday, January 14th, 2005 at 10:47 pm and is filed under Business and Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.