Friday, August 17, 2018

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