0

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
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Aman Chauhan
  • 95
  • 1
  • 3
  • 12

1 Answers1

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
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Thanks for providing the solution. But I guess I can also use RAISERROR () to throw the error to the parent Stored Procedure. – Aman Chauhan Feb 03 '17 at 07:30
  • The answer was to the question that was posted. If you find this answer correct, please mark it as accepted so that other people will know that the problem is solved. If you came up with a better answer, please post it and accept it. – Zohar Peled Feb 03 '17 at 08:04