This is a quick post, this question was asked recently

I have a stored procedure which is being called by some business objects and it’s working fine. I want to extend this stored procedure to call a new stored procedure (basically it will insert some of the passed in data into another table), but this isn’t working. How can I get the error output back from both stored procedures?

This is pretty simple to do if you capture the output from the first procedure and then you can use an output parameter to pass it back. Here is a very simple version of how it would work

This is our first proc, as you can see I hardcoded the value 1 so that we are sure this is returned from the first proc

CREATE PROC prtest1
AS
RETURN 1 --@@error
GO

This is our second proc, as you can see I hardcoded the value 2 so that we are sure this is returned from the first proc. You can also see that I store the return value from the stored procedure prtest1 in the @error and this is an output parameter

CREATE PROC prtest2 @error INT OUTPUT
AS
EXEC @error = prtest1
RETURN 2 --@@error
GO

Here is now how you bring both statuses back by using a combination of a return value and an output parameter. this part EXEC @SecondProc = prtest2 will get the return value.

This part @error = @FirstProc OUTPUT will get the value that is returned from the output parameter

DECLARE @SecondProc INT, @FirstProc INT
EXEC @SecondProc = prtest2 @error = @FirstProc   OUTPUT

SELECT @SecondProc  AS [2nd],@FirstProc AS [1st]

Output

————————————

2nd    1st
2   1   

I prefer to return errors that I capture from within the proc as output parameters, I will also use the _ERRORMESSAGE() function to return something that makes more sense to the user than a number. Below is an example

BEGIN TRY
     SELECT 1/0
END TRY

BEGIN CATCH
     SELECT ERROR_MESSAGE(), @@ERROR
END CATCH

Output

————————————————-

Divide BY zero ERROR encountered. 8134

What is better for the end user 8134 or “Divide BY zero ERROR encountered.”?