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 and stored it into a separate ErrorLog table for reference .
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 and stored it into a separate ErrorLog table for reference .
USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[uspLogError] Script Date: 8/17/2018 6:54:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
ON
GO
— usp_LogError stored procedure
logs error information in the ErrorLog table about the error
logs error information in the ErrorLog table about the error
— that caused execution to jump to
the CATCH block of a TRY…CATCH construct. This should be executed
the CATCH block of a TRY…CATCH construct. This should be executed
— from within the scope of a CATCH
block otherwise it will return without inserting error
block otherwise it will return without inserting error
— information.
CREATE PROCEDURE [dbo].[usp_LogError]
@ErrorLogID [int] = 0 OUTPUT — contains the
ErrorLogID of the row inserted
AS — by uspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;
— Output parameter value of 0 indicates that error
— information was not logged
SET @ErrorLogID = 0;
BEGIN TRY
— Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;
— Return if inside an uncommittable transaction.
— Data insertion/modification is not allowed when
— a transaction is in an uncommittable state.
IF XACT_STATE() = –1
BEGIN
PRINT ‘Cannot
log error since the currently transaction state is uncommittable. ‘
+ ‘Rollback the
transaction before executing uspLogError in order to successfully log error
information.’;
RETURN;
END
INSERT [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
);
— Pass back the ErrorLogID of the row inserted
SET @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT ‘An error occurred in
stored procedure uspLogError: ‘;
stored procedure uspLogError: ‘;
EXECUTE [dbo].[uspPrintError];
RETURN –1;
END CATCH
END;
GO