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