Rollback called Stored Procedures

[gdkz]

My question is, if a transaction contains a call to a store procedure that inserts data into a table and this procdure then calls another store procedure to update another table.

Will the entire transaction be rolled back if the insert or update fails in either nested stored procedure?

This is the code in the main store procedure. It executes stored procedure #1, stored procedure #1, executes store procedure#2.

BEGIN TRAN
EXEC(@@InputString) -- Execute the sp1
IF @@ERROR <> 0
Begin
--RAISERROR 50000
ROLLBACK TRAN
GOTO end_of_batch
END
COMMIT TRAN

Thanks in Advance!
Greg

 

[mihaildamjan]

In each called procedure you'll have to return to calling procedure, say , 0 if there were no errors, or return 1 if there were errors, and of course, rollback the executed steps.

In the calling procedure right after the procedure call you will have rollback transaction statement if return value is 1.

so,

declare @hold int
Begin tran
some code
more code
set @hold = call proc2
if @hold = 1
rollback
else
commit tran

so on.... so on