Error/Exception handling in SQL Server 2000

To do Exception handling in SQL Server 2000, we have to check @@ERROR system variable after each SQL statement in Stored procedure/function etc.

@@ERROR and @@ROWCOUNT
are the system variables updated by SQL engine after each SQL statement is processed /executed.

Ex :

INSERT INTO tableName (col1, col2) VALUES ('value1', 'value2') ;
PRINT @@ERROR

OR

PRINT 1/0 ;
PRINT @@ERROR

Here after the above INSERT statement , value of @@ERROR variable will be updated.

NOTE :
Even if we caught the @@RowCount variable after catching the @@ERROR in any local variable, @@RowCount value will be updated to 0 so its better to catch both variables in the same statement.
( And store it in local variable )

Ex :
SET @myERROR = @@ERROR
SET @myRowCOUNT = @@ROWCOUNT -- value of @@ROWCOUNT will be reset after above SET statement.

So the statement should be like this :
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT


CREATE PROCEDURE SP_ExceptionHandling
AS
DECLARE @myERROR int , -- Local @@ERROR
@myRowCount int -- Local @@ROWCOUNT

SET NOCOUNT
ON BEGIN TRAN

INSERT INTO tableName (col1, col2) VALUES ('value1', 'value2') ;
SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

INSERT INTO tableName (col1, col2) VALUES ('value1', 'value2') ;
SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

COMMIT TRAN -- No Errors, so go ahead
RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @myERROR
GOTO -- SP Ends here.