Old error checking still in use. @@ERROR

SQL 2000 error checking with @@ERROR and @@ROWCOUNT is still in use in many applications. Even with the advent of SQL 2005's TRY-CATCH feature you may still find this in old T-SQL or it might be the best fit for some applications. BUT make sure you understand how this works and fix those older legacy TSQL constructs that may have problems.

I found this pattern in SQL Stored Procedures for error checking at a client.

Example-1 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
rollback tran
return @@error
End

Does this work the way the original author expected? NO.
The intent is rollback an open transaction and report the error number encountered to the the caller of the procedure. What will always(99%) get reported is a SUCCESS value of Zero (0).
Why?
Because @@error ONLY maintains the condition of the “Last” SQL statement executed. In this case the ROLLBACK statement. This is also a problem when you introduce constructs that have IF conditions.

Example-2 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
if @@rowcount = 0
Begin
return @@error
End

Again the @@rowcount check resets the error condition and @@error returns 0.
Here are some rewrites of the above examples:

Example-1 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
If @@trancount > 0 rollback tran –-a good idea
return @errnum
End

And the proper way to check for row counts and error codes. You must grab both at the same time or you’ll get the wrong value in the 2nd variable.

Example-2 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
Select @ErrorVar = @@error, @RowCountVar = @@rowcount
if @RowCountVar = 0
Begin
return @ErrorVar
End

In SQL 2005 and above you should consider using TRY-CATCH for all Insert-Update-Delete SQL operations. These constructs are still popular and appropriate in some cases.
Cheers, Dave Winters