Call a Stored Procedure from another Stored Procedure.

Question

I want to call a SP from another SP. I know I can easily call it. But Problem is, if an error occurs in SP2 then I want to ROLLBACK SP1.

SP1
BEGIN Tran
[Some Code]
Call to SP2
[Some Code]

SP2
BEGIN TRAN
[Some Code]
[Error Comes]
ROLLBACK TRAN

This would rollback Tran in sp2 only. I want to RollBack SP1 as well, if an error occurs in sp2.

Any help would be appreciated.

Answer 1

One possibility is to create SP2 with an @ErrorCode INT OUTPUT parameter which indicates whether the caller needs to rollback or commit.

Answer 2

Try RAISERROR in SP2.

Answer 3

Create an output parameter in your second SP which is of type bit, indicating wether an error occured or not. Based on this, you can rollback SP 1.

Answer 4

Seems people have issues with other information sites...

The gist of it is that the parent procedure will through an exception when trying to perform a ROLLBACK as the child already has. The way around this is to have the parent check the @trancount before committing.

http://forums.asp.net/t/1259708.aspx

Create procedure [dbo].[parent]  as Begin Transaction Begin Try
    Exec Child End Try Begin Catch
    If @@Trancount > 0
        RollBack End Catch Commit 


Create procedure [dbo].[Child]  as Begin Transaction Begin Try
    --Do inserts here End Try Begin Catch
    If @@Trancount > 0
        RollBack
    RAISERROR('Error Occured',16,1) End Catch Commit

Answer 5

you can use an error code like that (i m not writing the code just how do i do if i were you)

SP1
DECLARE ReturnVal
BEGIN TRAN
CODE
CALL SP1 ReturnVal output
IF ReturnVal=errorvalue
ROLLBACK TRAN

SP2
DECLARE ReturnVal output
BEGIN TRAN
CODE
ERROR
SET ReturnVal=errorVal
ROLLBACK
RETURN ReturnVal

Answer 6

It doesn't sound like you need the nested transactions. Try controlling the commit/rollback with try blocks (psuedocode):

begin try
  begin trans
  do stuff
  call other sp
  do more stuff
  commit trans
end try
begin catch
  rollback trans
  do something here to report failure to app
end catch

If an error occurs anywhere within the try block, including withing the other sp, the control will pass to the catch block and rollback the transaction.