Exception handling in Sql server 2000

When I was working on a project, I had written a sproc which runs in sql server 2005. I had used Try Catch blocks to handle the exceptions and roll back the transaction for any exception. My script was working fine with sql-2005. But when I had to deploy on production, I noticed that we had sql server 2000 on which our legacy database was sitting. When I ran my script it was failing to create the sproc due to try catch blocks. After doing a while research on it I found that sql server 2000 deals with exceptions in different way.

When any exception is raised then @@ERROR will be set to a non zero value. If you want to check whether exception has araised you should check

@@ERROR <>0 -> Exception occurred

@@ERROR =0 -> No Exception

To handle exceptions in sql server 2000 we need to check the condition @@ERROR <>0 for every insert/update/delete statement and based on that we need to roll back the transaction if used.

Example:

BEGIN TRAN

DELETE EMP WHERE EmployeeType = ‘ProjectManager’

IF (@@ERROR <>0)

GOTO HandleError;

UPDATE EMP SET Salary = Salary * 1.30, EmployeeType = ‘ProjectManager’ WHERE EmployeeType = ‘TeamLead’

IF (@@ERROR <>0)

GOTO HandleError;

COMMIT TRANS

RETURN;

HandleError:

ROLLBACK TRAN

RETURN;