Insert Failed but Stored Procedure Continued

Question

Insert Failed but Stored Procedure Continued

clarmatt73

SSCrazy

Points: 2297
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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: 2297 More actions · Answer 2

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: 1004504 More actions · Answer 3

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

Insert Failed but Stored Procedure Continued

Cookies on SQLServerCentral