Rollback with a stored procedure

Question

I have a complex script with many stored procedures that I like to rollback.

I ask if in a situation like this:

BEGIN TRANSACTION;  

INSERT INTO ValueTable VALUES(1);  
INSERT INTO ValueTable VALUES(2);  

EXEC MyStoredProcedure

ROLLBACK;

All update/insert inside MyStoredProcedure will rollback.

Thanks

Depends what happens in `MYstoreProcedure` and if it has it's own explicit transactions. — Thom A, Mar 08 '21 at 12:06
Of course, this looks very simple for you to test your Development environment. Why didn't you? — Thom A, Mar 08 '21 at 12:07
First executed `commit` within any procedure will commit the whole transaction. — Serg, Mar 08 '21 at 12:12
Suggested reading: [Handling Transactions in Nested SQL Server Stored Procedures](https://www.mssqltips.com/sqlservertip/4897/handling-transactions-in-nested-sql-server-stored-procedures/). — HABO, Mar 08 '21 at 14:33
@Serg that is incorrect, see @@trancount. You can test your assertion easily and prove it to be false. — Stu, Mar 08 '21 at 14:35
@HABO you posted a link to a 404 error, however this question arises frequently and has been documented many times eg https://stackoverflow.com/questions/9692734/sql-server-nested-transactions-in-a-stored-procedure — Stu, Mar 08 '21 at 14:39
@sTTu Odd, I copied a fresh URL for the comment and the link works for me now. I can refresh the target page, so it isn't just something lurking in my cache. — HABO, Mar 08 '21 at 14:43

score 1 · Accepted Answer · answered Mar 08 '21 at 12:50

1

Yes, provided you have

set xact_Abort on

at the start of your transaction.

answered Mar 08 '21 at 12:50

Stu

30,392
6
14
33

score 0 · Answer 2 · answered Mar 08 '21 at 12:29

You can use try catch to execute rollback if any error occurs otherwise execute commit. Here goes a simple example.

BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO TABLE1 VALUES('A',3,'E');   
    
INSERT INTO ValueTable VALUES(1);  
INSERT INTO ValueTable VALUES(2);  

exec MYstoreProcedure

END TRY
BEGIN CATCH 
IF @@TRANCOUNT > 0
    BEGIN   
    ROLLBACK TRANSACTION;
    throw;
    END 
END CATCH;
IF @@TRANCOUNT > 0
    BEGIN   
    COMMIT TRANSACTION;
    PRINT 'Successfully Completed'
    END

And what do you do if the transaction is uncomittable in the `catch`? this is why you should use `xact_abort` — Charlieface, Mar 08 '21 at 13:38

score 0 · Answer 3 · answered Nov 17 '22 at 17:39

0

Here is an example of Handling SQL Server Errors in Nested Procedures https://stackoverflow.com/a/74479802/6204480

answered Nov 17 '22 at 17:39

yikekas

129
1
5

Rollback with a stored procedure

3 Answers3