Does a transaction cover the subroutines?

Question

When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the stored procedure which is executed?

Answer 1

Yes the rollback transaction would rollback all changes made inside the stored procedure except if the stored procedure was itself to issue an unbalanced COMMIT which would raise an error

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.

but still lead to the transaction being committed. Both cases shown below.

CREATE TABLE T(proc_name sysname)

GO

CREATE PROC proc_1
AS
INSERT INTO T VALUES ('proc_1')

GO

CREATE PROC proc_2
AS
INSERT INTO T VALUES ('proc_2')
COMMIT;

GO

BEGIN TRAN
EXEC proc_1 
ROLLBACK
/*Table is empty. Transaction rolled back*/
SELECT * FROM T

GO

BEGIN TRAN
EXEC proc_2 
ROLLBACK
/*Table is not empty. Transaction was committed*/
SELECT * FROM T