November 24, 2014 at 12:08 pm
create proc proc1 (@param1 int)
as
begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.
November 24, 2014 at 12:47 pm
Any rollback rolls back all the nested transactions.
Gerald Britton, Pluralsight courses
November 24, 2014 at 1:00 pm
gravitysucks (11/24/2014)
create proc proc1 (@param1 int)
as
begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.
Quick note, a rollback in a "nested" transaction will rollback all the transactions. There is in fact no such thing as a nested transaction, simply a counter that's iterated. Consider this code sample
😎
USE tempdb;
GO
SET NOCOUNT ON;
SELECT @@TRANCOUNT AS TRANCOUNT;
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
SELECT @@TRANCOUNT AS TRANCOUNT;
ROLLBACK TRAN
SELECT @@TRANCOUNT AS TRANCOUNT;
Output
TRANCOUNT
-----------
0
TRANCOUNT
-----------
4
TRANCOUNT
-----------
0
November 24, 2014 at 1:33 pm
Eirikur Eiriksson (11/24/2014)
gravitysucks (11/24/2014)
create proc proc1 (@param1 int)
as
begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch
i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.
Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.
Quick note, a rollback in a "nested" transaction will rollback all the transactions. There is in fact no such thing as a nested transaction, simply a counter that's iterated. Consider this code sample
😎
USE tempdb;GO
SET NOCOUNT ON;
SELECT @@TRANCOUNT AS TRANCOUNT;
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
SELECT @@TRANCOUNT AS TRANCOUNT;
ROLLBACK TRAN
SELECT @@TRANCOUNT AS TRANCOUNT;
Output
TRANCOUNT-----------
0
TRANCOUNT
-----------
4
TRANCOUNT
-----------
0
Thank you. I should have tried that before prior posting the question. The articles on the net i read made it sound like it was not as simple as that. I will give it a try.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply