Error Handling...

One of the aspects in T-SQL development, especially with writing stored procedures is Error Handling. It is important to trap errors, log the errors and provide a proper exit from a stored procedure. One of concepts which was introduced in the .NET framework, has been incorporated into T-SQL. It is the BEGIN TRY..END TRY and BEGIN CATCH..END CATCH block. this feature allows one to keep the code blocks within the stored procedure modular. Here is an example of the use of TRY CATCH block within the stored procedure.
BEGIN TRY
      INSERT INTO tablea
      (col1,col2,col3)
      SELECT col1,col2,col3 FROM testtable
END TRY
BEGIN CATCH
      INSERT INTO table_error_log
      ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)
     SELECT ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_LINE () as ErrorLine,
      ERROR_PROCEDURE() as ErrorProcedure,
      ERROR_MESSAGE() as ErrorMessage
END CATCH

One of the neat things while using the CATCH block is the availability certain standard functions which provides the Error Procedure and Error Message. These functions can provide valueable information about the Error.