SQL Server Performance

Calling a stored procedure from a stored procedure

Discussion in 'General DBA Questions' started by Chappy, Jan 28, 2003.

  1. Chappy New Member

    Calling a stored procedure (SPA) using EXECUTE from an existing stored procedure (SPB)..

    How can I catch the RETURN value ? I understand this differs from an output parameter, which I would rather not use.

    If I RAISERROR in SPA, how can I trap this in SPB ?

    Any resources on this would be useful. BOL doesnt seem to cover it that much that I can see, though Im probably looking in all the wrong places.

    Thanks
  2. HarryArchibald New Member

    Something like this inside SPA should work.
    declare @returncode smallint
    exec @returncode =spb
    if @returncode <>0
    DO SOMETHING
  3. Chappy New Member

    Thanks Harry, that seems to be working now.
    My main problem remaining is how RAISERROR reacts.

    If an error is raised in SPA, I need to be able to trap this in SPB, because SPB wraps a transaction around the call plus a few other operations, and I need to be able to rollback the transaction.
  4. royv New Member

    You should be able to do this. Is there a specific issue you are having?


    "How do you expect to beat me when I am forever?"
  5. Chappy New Member

    Yes. Though I accept it could be a design issue more than implementation.

    We have a win32 app which needs to be able to call the SPB 'wrapper', or sometimes it may just call SPA. Invalid parameters need to be signalled by the stored procedures and handled by the app. Due to existing code this is done by structured exception handling, and hence the need for RAISERROR to signal the error, and allow ADO to create an exception.

    both SPA and SPB are wrapped in a transaction.
    So when SPB calls SPA, if SPA calls RAISERROR, it appears that control never returns to SPB, and therefore I can neither commit or rollback the transaction started at the top of SPB.

    Hope this is all as clear as mud

    Thanks for any advice
  6. Chappy New Member

    Just to reiterate, my question is how to force control to return to SPB, even if SPA raises an error. And if an error in SPA is raised, how to trap it in SPB.
  7. royv New Member

    I'm not sure, but this is what I'm thinking. When trying to return a RAISERROR to ADO, the serverity level must be 16 or higher. The problem with this is that this terminates the SQL Server connnection. As a result, this *MAY* be the reason why the error does not propogate to SPB. However, since you are in a nested transaction, the good news is that you can do a ROLLBACK in SPA and it will rollback the whole thing. This is what I have found to be true, I might be wrong.


    "How do you expect to beat me when I am forever?"
  8. Chappy New Member

    That sounds about right for RAISERROR. If my app calls SPB, which then calls SPA, and an error occurs, are you saying that a sinle ROLLBACK TRANSACTION would rollback both transactions (bearing in mind @@TRANCOUNT = 2 at this point)?

    Im starting to think this is getting a bit messy. Perhaps I should split the actual functionality into stored procedures which simply return 0 or 1, and then the app calls 1 of 2 wrapper procedures which do the actual validation, and call RAISERROR if needed.

  9. royv New Member

    That is the assumption I was under(@@TRANCOUNT=2). This is what I meant by a nested transaction (@@TRANCOUNT>1). If your secondary solution can be implemented, it definitely sounds cleaner and better.


    "How do you expect to beat me when I am forever?"
  10. Chappy New Member

    Yeah, I think your right. Thats what Ill do.<br />Cheers for the advice<img src='/community/emoticons/emotion-1.gif' alt=':)' />
  11. Argyle New Member

Share This Page