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


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:55 PM
Points: 78, Visits: 248
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 1, 2012 11:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 7,125, Visits: 12,721
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
Post #1338954
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse