Rollback an entire stored procedure

Question

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

Answer 1

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