How to handle SQL error in Stored Procedure

[alok pandey]

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;