XACT_ABORT with recursive stored proc

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  July 19, 2012 at 9:34 am

  #261404

  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;

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  August 1, 2012 at 11:34 pm

  #1520645

  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