T-SQL Programming – Stored procedure for Error handling

Error handling is an important aspect
of any programming language. Like other programming languages, in T-SQL
programming also, we use the TRY_CATCH block to capture the error.  To capture the detailed error, we use the
system defined function provided by SQL Server. In order to reduce the
rewriting of same code & utilizing the code reusability. We can use the
below stored procedure in the catch block to catch the error in the stored
procedure.

 USE [MyDatabase]

GO

/****** Object:  StoredProcedure [dbo].[usp_PrintError]    Script Date: 8/17/2018 5:47:35 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER
ON

GO

— usp_PrintError stored procedure
prints error information about the error that occured

— It executes when jump to the
CATCH block of a TRY…CATCH construct.

— It executes within the scope of a
CATCH block 

— In case of no error, it will not
without print any error information.

CREATE PROCEDURE [dbo].[usp_PrintError]

AS

BEGIN

    SET NOCOUNT ON;

    — Print error information.

    PRINT ‘Error ‘ + CONVERT(varchar(50), ERROR_NUMBER()) +

          ‘, Severity ‘ + CONVERT(varchar(5), ERROR_SEVERITY()) +

          ‘, State ‘ + CONVERT(varchar(5), ERROR_STATE()) +

          ‘, Procedure ‘ + ISNULL(ERROR_PROCEDURE(),
‘-‘) +

          ‘, Line ‘ + CONVERT(varchar(5), ERROR_LINE());

    PRINT ERROR_MESSAGE();

END;

GO