CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Shivam Sharma
Shivam Sharma

Reputation: 107

Rollback an entire stored procedure

I have a stored procedure with multiple update statements.I dont want to use try catch.How can I rollback the stored procedure and get back the original table?

can something like this work -

begin transaction t1 spName rollback transaction t1

Upvotes: 5

Views: 7374

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

Yes you can wrap everything into a sproc into a transaction

begin tran
exec testproc

commit tran
--rollback tran --for condition

It works fine even for commit as well rollback

If for inside the sproc you need to open another transaction then you need to capture 

DECLARE @vTranCount   INT = @@TRANCOUNT

--Commit
IF (@vTranCount = 0 AND @@TRANCOUNT <> 0) COMMIT TRANSACTION --Commit if the Tran is created by this sproc

--rollback during catch
IF(@vTranCount = 0 AND @@TRANCOUNT > 0) ROLLBACK TRANSACTION --Rollback if the Tran is created by this sproc

Upvotes: 8

Related Questions