July 19, 2012 at 9:34 am
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;
August 1, 2012 at 11:34 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply