Get raised exception from stored procedure as output variable

Question

I am having problem in storing the exception raised from a stored procedure.

For example I am having two stored procedures P_Add And P_GetAdd.
P_Add returns an exception on divide by zero as shown, procedure P_GetAdd executes P_Add.
I want to return the exception raised by P_Add to the code from where P_GetAdd is executed.
P_Add excpets a parameter which was not supplied is the error. Help me to get the result. Thanks.

create procedure P_add
(
    @ErrorMessage varchar (100) OUTPUT
)
as
Declare @a int
Declare @b int
declare @c int

set @a = 1
set @b = 0

Begin try
    SET @c = @a/@b
end try
begin catch
   SET @ErrorMessage = ERROR_MESSAGE();
End catch

create procedure P_getADD
(
@ErrorMessage1 varchar (100) OUTPUT
)
AS

IF EXISTS (
        SELECT *
        FROM sysobjects
        WHERE id = object_id(N'[dbo].[P_add]')
        AND OBJECTPROPERTY(id, N'IsProcedure') = 1   
        )
BEGIN
    EXEC P_add  @ErrorMessage = @ErrorMessage1
END

Answer 1

Assuming I understand the question, you need to mark @ErrorMessage1 as output when executing the P_add stored procedure:

CREATE PROCEDURE P_getADD
(
    @ErrorMessage1 varchar (100) OUTPUT
)
AS

IF EXISTS (
        SELECT *
        FROM sysobjects
        WHERE id = object_id(N'[dbo].[P_add]')
        AND OBJECTPROPERTY(id, N'IsProcedure') = 1   
        )
BEGIN
    EXEC P_add @ErrorMessage1 OUTPUT
END