Insert Failed but Stored Procedure Continued

  • clarmatt73

    SSCrazy

    Points: 2273

    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

    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

    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

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply