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.
INSERT INTO tablea
SELECT col1,col2,col3 FROM testtable
INSERT INTO table_error_log
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
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.