sql-serversql-server-2008raiseerror

How to get the message of RaiseError of a procedure in another procedure

I have two procedures PROC_A and PROC_B.

In both procedure transaction are managed.

PROC_B throws an error by calling RAISEERROR and passes a message in certain condition 

RAISERROR ('Initiator is Inactive', 16, 1, 'Approve Transaction');

I am executing (calling) PROC_B in PROC_A. Now I want to get the error message thrown by PROC_B in PROC_A.

How can I do that?

Solution

  • Try this:

    CREATE PROCEDURE dbo.TestA
AS
BEGIN
    RAISERROR ('Initiator is Inactive', 16, 1, 'Approve Transaction'); 
END;
GO

CREATE PROCEDURE dbo.TestB
AS
BEGIN
    BEGIN TRY
    EXEC dbo.TestA;
    END TRY

    BEGIN CATCH
    SELECT ERROR_MESSAGE();
    END CATCH
END;
GO

EXEC dbo.TestB;
GO

DROP PROCEDURE dbo.TestB;
DROP PROCEDURE dbo.TestA;