Nested procedures - if N fails, rollback A through M?

[LNBruno]

Seems like these things always come up on Fridays!

I've been tasked with implementing transactional processing for a particularly nasty set of nested stored procedures. There's one entry-point procedure that calls 12 others, each of which does a bunch of calculations and then inserts or updates various tables. They all comprise a single business function - so if any one of them fail, all previous transactions should be rolled back.

I've toyed with the idea of using output parameters from the nested procs to return the primary key and function performed (insert or update) back to the entry-point proc. I've also considered having each nested proc return the T-SQL string which is currently execute and having the entry-point proc be the execution point for all of them inside a transaction block. Neither of these approaches seem particularly good/correct.

Ideas, anyone?

< M!ke >

 

[SQLDenis]

lookup SET XACT_ABORT in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[LNBruno]

Thanks for the quick reply, but that's not exactly what I'm looking for. Let me try again with some code sample.

First, create a table and a few procedures:

CREATE TABLE AbortTest (TestID int, TestValue varchar(50))
GO

CREATE PROCEDURE TestOne
AS
INSERT INTO AbortTest VALUES (5, 'TEST ONE')
GO

CREATE PROCEDURE TestTwo
AS
INSERT INTO AbortTest VALUES (10, 'TEST TWO')
GO

CREATE PROCEDURE TestThree
AS
DECLARE 
    @intOne int
    , @intTwo int

SET @intOne = 10
SET @intTwo = 0
SELECT @intOne/@intTwo --doomed to fail
GO

CREATE PROCEDURE TestFour
AS
INSERT INTO AbortTest VALUES (20, 'TEST FOUR')
GO

Then execute this snippet from the entry-point procedure (which calls all the others):

SET XACT_ABORT ON
GO
EXECUTE TestOne
EXECUTE TestTwo
EXECUTE TestThree -- when this fails, I need to rollback the inserts from TestOne and TestTwo
EXECUTE TestFour  -- the XACT_ABORT ON prevents this from running
SET XACT_ABORT OFF
GO

And, finally, to check the results:

SELECT * FROM AbortTest --should return no records

< M!ke >

 

[SQLDenis]

try this

SET XACT_ABORT ON
begin tran
EXECUTE TestOne
EXECUTE TestTwo
EXECUTE TestThree -- when this fails, I need to rollback the inserts from TestOne and TestTwo
EXECUTE TestFour  -- the XACT_ABORT ON prevents this from running
commit tran
SET XACT_ABORT OFF



SELECT * FROM AbortTest --should return no records

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[LNBruno]

You are truly BRILLIANT!

I am truly an idiot. After using TRANS everywhere else, why didn't I think of using it here???

Don't even need the XACT_ABORT...

Thanks, Denis! You've saved my afternoon!

Cheers!




< M!ke >

 

[SQLDenis]

>>Don't even need the XACT_ABORT...

yes you do!
because of nontrapable errors

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[LNBruno]

Oh, man! Totally right - thanks! I'd double star you if I could!

< M!ke >