SP inside SP and transactions

[Puiu]

Let's say I have a stored procedure SP_Master with a code similar to this:

 

create proc sp_master
as
begin

begin tran

exec sp1
exec sp2

update table1 set...

rollback
end

 

My question: the rollback statement will also rollback the changes made by sp1 and sp2?

 

Thanks

[PlausiblyDamp]

Under SQL a ROLLBACK statement rolls back all active transactions. In your example this would include updates performed by both sp1 and sp2.