Microsoft KB Archive/811034

= PRB: Inconsistent Error Handling Behavior Occurs with @@ERROR in SQL Server =

Article ID: 811034

Article Last Modified on 5/10/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



SYMPTOMS
Error handling behavior with the @@ERROR system function is inconsistent in Microsoft SQL Server. If you use @@ERROR, some errors, such as fatal errors, cannot be handled in a Transact-SQL batch or a stored procedure.



CAUSE
In some situations, error handling in a Transact-SQL batch or a stored procedure by using @@ERROR is not possible. After the error occurs, the Transact-SQL batch or the stored procedure is aborted, all uncommitted transactions are not rolled back, and the execution of the statements stops. Because of this behavior in SQL Server, it is not possible to handle all kinds of errors by using @@ERROR in a Transact-SQL batch or a stored procedure.



WORKAROUND
To work around this problem, you can do one of the following:
 * Handle the errors at the client application.
 * Handle the errors outside the Transact-SQL batch or the stored procedure.

If you want to handle the errors in the Transact-SQL batch or the stored procedure, handle the problem that causes the error before you run the Transact-SQL statement.

For example, if you are referring to a table that does not exist in the database, check whether the table exists or not. If the table does not exist you can then display a user-defined message. Here is an example:

create procedure TestProcedure @id int as

BEGIN Declare @price int

SELECT @price = price FROM   Books WHERE  bookid = @id -- Refer to a table that does not exist

IF @@error <> 0 Begin print 'Error Occurred.' End

END go

Exec TestProcedure 10 go When you run the procedure, you receive the following error message:

Server: Msg 208, Level 16, State 1, Procedure test, Line 4

Invalid object name 'Books'.

To handle the error in a stored procedure, you must check whether the table exists or not. For example: create procedure TestProcedure1 @id int as Declare @price int Declare @count int

BEGIN IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) – See if table exists Begin

SELECT @price = price FROM   Books WHERE  bookid = @id

IF @@error <> 0 Begin print 'Error occurred.' End End Else Begin print 'Table does not exist.' End

END go

Exec TestProcedure1 10 go

