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
No comments:
Post a Comment