1

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?

  • You've tagged both SQL server and mysql. Which are you using? – Philᵀᴹ Feb 14 '13 at 10:15
  • @Phil ms sql server – Uğur Gümüşhan Feb 14 '13 at 10:15
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.