Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

sql server 2005

How do I put a stop on my procedure on error? There're many parts of my procedure, and want it to stop on any part that it occur an error, i.e, when it can't append the records to the table because of key violation, I want the procedure to stop there and not executing the next part. Please help me with the syntax.
0
HNA071252
Asked:
HNA071252
2 Solutions
 
OCDanCommented:
In short there isn't really a quick way to do this, but THIS LINK explains different ways to do error catching very well and why one method is better than another.
0
 
arnoldCommented:
In a way it all depends on the meaning of "error."

Are you looking at an insert/update and get an error response that the update you are trying to run is invalid or the user executing the stored procedure does not have the rights to the underlying table.
http://www.novicksoftware.com/tipsandtricks/tips-erorr-handling-in-a-stored-procedure.htm

there are @error and system events that you can check and rollback the transaction.
Or have conditions on continuing in the stored procedure as long as no prior errors were seen.
0
 
TempDBACommented:
When you define your stored procedure, set xact_abort property to on. This will ensure the rollback of the transaction and stops the execution of the procedure. Check out the following link:-

http://msdn.microsoft.com/en-us/library/ms188792.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now