Thursday, March 28, 2013

Re-Throw Error for Logging

Sometime errors in Store Procedure go unexpected. If there is no proper error handling in application side then it will be difficult to identify the database problem.

This error handling machanism provides the clean way to catch and re-throw the  errors to the calling application and also log in the table if necessary to make troubleshoot easy.

Create your main Store Procedure to handle transactions and error message properly
In the catch block call the [P_RethrowError]  SP to record /print the errors


CREATE PROCEDURE DoSomething
AS

SET NOCOUNT ON;

DECLARE @RC INT,
            @TranCount INT

/*Set the error code to 99*/
SET @RC=99
SET @TranCount = @@TRANCOUNT

BEGIN TRY

/*Check if the transaction is already open*/
IF @TranCount = 0
      BEGIN TRANSACTION  

/*  Do Something  Write your logic here*/


/* commit if the scope of transaction is the SP only*/
IF @TranCount = 0 AND @@TRANCOUNT > 0
      COMMIT TRANSACTION

-- If everything goes OK then set the error code to 0 (success)  
SET @RC=0

RETURN @RC
END TRY
BEGIN CATCH

/* Rollback if the scope of transaction is the SP only*/
IF @TranCount = 0 AND @@TRANCOUNT > 0
      ROLLBACK TRANSACTION

SET @Rc = ERROR_NUMBER()

/* Call the rethrow error SP to print or record the exception*/
EXEC [P_RethrowError] @logError= 1,  @Debug =0

SET NOCOUNT OFF;
RETURN @Rc

END CATCH
GO




[P_RethrowError]  SP to record /print the errors


CREATE PROCEDURE [P_RethrowError]
      @logError BIT = 0,
      @Debug BIT = 0
AS
BEGIN
    
IF ERROR_NUMBER() IS NULL
    RETURN;

DECLARE
      @Msg NVARCHAR(255),
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);


SELECT
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');


SELECT @Msg=ERROR_MESSAGE();

SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ @Msg;

IF @Debug=1
BEGIN
      PRINT @ErrorNumber
      + ' - ' + @ErrorProcedure
      + ' - ' + @ErrorMessage
      + ' - ' + GETDATE()
END

IF @logError=1
BEGIN

      INSERT INTO [T_Errors]
      ( ErrorNumber , ErrorSource , ErrorMessage, ErrorTime)
      VALUES
      (@ErrorNumber,@ErrorProcedure,@ErrorMessage,GETDATE())

END

END


Provide the full qualified name in SP and Error table if we are using any schema based architecture in our database.

Post a Comment