multiple store procedure in single transaction batch

• deba_20032004

  Default port

  Points: 1476

  More actions

  June 21, 2010 at 7:19 am

  #74068

  Dear Sir,

  I want to write a procedure which contains several store procedures in

  single batch transaction. my main purpose is if all sp is successfull then

  commit transaction is executed if any one of them fails then execute rollback transaction.

  but which one will sucessfull and which one will be fails how to keep the track, I can't understand. so please help me with a sample code.

  Thanking You

  Debasis Bag

  Suppose :-

  Create procedure xx

  set nocount on

  begin Transactions

  Begin

  --Inserting data table 1

  sp_InsertData1

  --Inserting Data Table 2

  sp_InsertData2

  End

  If @@error<>0 then goto errorHandler

  commit transaction

  return

  errorHandler:

  rollback transaction

  return @@Error

  Go

  Note 'Here is no way to control track of error in one single transaction batch.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 21, 2010 at 7:23 am

  #1182718

  You could check for @@error <> 0 after each procedure call and use RAISERROR with a meaningful error message.

  -- Gianluca Sartori

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  June 21, 2010 at 9:49 am

  #1182815

  You could also use return values to validate success/failure

  declare @retval int

  --Inserting data table 1

  exec @retval = sp_InsertData1

  -- SUCCESS: @retval = 0

  -- FAILURE: @retval = 1

  If @retval = 1 then goto errorHandler

  --Inserting Data Table 2

  exec @retval = sp_InsertData2

  If @retval = 1 then goto errorHandler

  _____________________________________________________________________
  - Nate

  @nate_hughes