Raiserror with nested stored procedures

[Former Member]

I use raiserror in nested stored procedures.  How do I get the message (format string) from the nested stored procedure and append it to the calling stored procedure raise error.  Something similar to: exec sp1 if @@error...   @msg = get the raise  error from sp1 and add it to this error message   raiserror 25000  @msg

[former_member89972 · Accepted solution]

Hi John

Top of the head :

1.

Create a temp table (hash table) in the parent proc and populate it in child proc on error.

When control goes back to parent proc you can read status from the temp table.

Does not need any special privileges. Temp table will dropped when parent procedure completes.

2.

Same results can also be achieved with user defined application contexts.

Application contexts can be used to pass a limited amount of data between stored procedures in a session.  User will need privileges to  set/get application contexts.

HTH

Avinash

[Former Member]

This worked great.  I create the temporary table in PowerBuilder and then I call a stored procedure which calls other stored procedures....   and it keeps populating the #errorlog table and everything was rolled back except this temp table.  which gets returned back to PowerBuilder and in PowerBuilder I then populate the permanent error table.  This temp table doesn't get reset.  It solved my problem!  thank you!