Insert Failed but Stored Procedure Continued

• clarmatt73

  SSCrazy

  Points: 2273

  September 7, 2017 at 10:50 am

  #149373

  Hi,

  We have a Stored Procedure being run by an SSIS Package which goes through records in a table (Table A) and inserts/updates records in another table (Table B). Once the Insert/Update has finished the records in Table A are stamped with today's date. We have had an instance where the Insert has failed correctly due to the conditions of an index being breached on Table B however Table A has still been stamped with today's date and the rest of the Stored Procedure has been completed. The Procedure is broken down into 4-5 Begin and End sections and I just wanted to understand if it is normal for the procedure to complete even though part of it has failed.

  Thanks

  Matt

• Gail Shaw

  SSC Guru

  Points: 1004424

  September 7, 2017 at 11:47 am

  #1958621

  Yup. Normal behaviour. Most errors terminate the statement, not the batch.

  If that's not what you want, you need some error handling, and probably some transactions.
  http://www.sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
  The  blog post talks about transactions, but the behaviour's the same with or without transactions

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• clarmatt73

  SSCrazy

  Points: 2273

  September 8, 2017 at 4:09 am

  #1958697

  Hi GilaMonster,

  Thanks for the response, it actually works in our favour I think as the procedure sends an email at the end so we can see where the Insert stopped.

  Cheers,

  Matt

• Gail Shaw

  SSC Guru

  Points: 1004424

  September 8, 2017 at 4:41 am

  #1958699

  You can have error handling and transactions to roll back partial operations and still have it email you.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass