Raiserror with nested stored procedures
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
Avinash Kothare replied
Top of the head :
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.
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.