Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XACT_ABORT with recursive stored proc Expand / Collapse
Author
Message
Posted Thursday, July 19, 2012 9:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 72, Visits: 220
Hi,
we use XACT_ABORT to rolback if statements failed.

I have a scenario where I have a stored proc that call other stored proc inherently.

Store Proc Main hold following

Begin
Exec StoredProc1; -- this stored proc perform insert
Exec StoredProc2; -- this stored proc perform insert.Update
End;

Now can I use XACT_ABORT in main stored proc, so if any of sp failed (StoredProc1,StoredProc2....), it may rollback all ?

Begin
xact_abort on
Exec StoredProc1; -- this stored proc perform insert
Exec StoredProc2; -- this stored proc perform insert.Update
xact_abort off
End;
Post #1332306
Posted Wednesday, August 01, 2012 11:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
XACT_ABORT may work, but I would recommend you use TRY/CATCH instead for managing exceptions and transactions.

BEGIN TRY;

BEGIN TRAN;

Exec StoredProc1; -- this stored proc perform insert
Exec StoredProc2; -- this stored proc perform insert.Update

COMMIT;

END TRY
BEGIN CATCH
IF XACT_STATE() != 0
ROLLBACK TRAN;

DECLARE @err_str VARCHAR(2048),
@err_sev INT,
@err_state INT;

SELECT @err_str = ERROR_MESSAGE(),
@err_sev = ERROR_SEVERITY(),
@err_state = ERROR_STATE();

RAISERROR(@err_str, @err_sev, @err_state);
END CATCH;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1338954
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse