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;
