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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy