How to handle SQL error in Stored Procedure

When writing SQL procedures, it is really important to handle errors cautiously. Having that in mind will probably save your efforts, time and money. I have been working with MS-SQL 2000, MS-SQL 2005, MS-SQL 2008 (I have not got the opportunity to work with MS-SQL 2008 R2 yet) for many years now and I want to share with you how I handle errors in T-SQL Stored Procedure. This code has been working for many years now without a hitch.

BEGIN TRANSACTION;
BEGIN TRY
— Code in transaction go here COMMIT TRANSACTION;
Declare @myVal int
Set @myVal = ‘dfgdfd’
Select @myVal
END TRY
BEGIN CATCH
— Rollback on error
ROLLBACK TRANSACTION;
— Raise the error with the appropriate message and error severity
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
Select @ErrMsg as ErrMessage,@ErrSeverity as ErrSeverity
–RAISERROR(@ErrMsg, @ErrSeverity, 1);

END CATCH;