SQL Server Performance Forum – Threads Archive

Calling a stored procedure from a stored procedure

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

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

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.

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?"

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

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.

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?"

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.

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?"

Yeah, I think your right. Thats what Ill do.<br />Cheers for the advice<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

This article might be helpful: Error Handling in T-SQL: From Casual to Religious
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00f15.asp It discusses error handling in nested stored procedures. /Argyle

]]>