Error handling with a called stored procedure

  • Stored procedure OUTER_TEST calls stored procedure INNER_TEST.

    If an error in INNER_TEST, how do I capture the error message (to store in a table). When I have a try-catch in OUTER_TEST, I can only access a later error message and not the initial one from INNER_TEST?

  • Try out the following code. You will see that the error generated in the first proc is captured in the second.

    CREATE OR ALTER PROCEDURE dbo.ErrorTest1
    AS
    BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
    SELECT 1 / 0;
    END TRY
    BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE()
    ,@ErrorSeverity = ERROR_SEVERITY()
    ,@ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
    END;
    GO

    CREATE OR ALTER PROCEDURE dbo.ErrorTest2
    AS
    BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
    EXEC dbo.ErrorTest1;
    END TRY
    BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE()
    ,@ErrorSeverity = ERROR_SEVERITY()
    ,@ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
    END;
    GO

    EXEC dbo.ErrorTest2;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It might be a better option - if using TRY/CATCH - to THROW the error instead of using RAISERROR.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    It might be a better option - if using TRY/CATCH - to THROW the error instead of using RAISERROR.

    I'd be interested to hear your reasoning.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply