Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You've tagged both SQL server and mysql. Which are you using? –  FreshPhilOfSO Feb 14 '13 at 10:15
    
@Phil ms sql server –  Uğur Gümüşhan Feb 14 '13 at 10:15
add comment

1 Answer

up vote 3 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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