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