CODEWITHSUNDEEP
sqlsql-servert-sql
user3449922
user3449922

Reputation:

Rollback with a stored procedure

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

Upvotes: 0

Views: 1114

Answers (3)

yikekas
yikekas

Reputation: 147

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

Upvotes: 0

Stu
Stu

Reputation: 32614

Yes, provided you have

set xact_Abort on

at the start of your transaction.

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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

Upvotes: 0

Related Questions