Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

error handling in sql server 2000

Status
Not open for further replies.

washaw

Programmer
Feb 13, 2008
48
I am writing a big query and trying to do some error handling.

I have a squence of insert statements, and what I want is if one insert statenet failes I don't want the whole process fail insttead it ignores the error and proceedes to the next insert statement; ( on error move next kind of thing)

I tryed it using @@error, not still working. what should I do

Thanks


 
The simplest way would be to validate the data before your insert and only attempting the insert if the criteria for a successful insert are met.

There are loads of suggested error handling routines out there, but pre validated data I've always found to be best.
 
First, When I am inserting data, I do all the clean up in work tables before attempting to insert into the production tables. Then I can reun a set-based statement that I am sure will run (unless I have a connection or server problem in which case I am happy to have the whole thing rollback). I would never try to directly import to a production table without the step of making sure all the data is correct before doing the insert.

you can also put each insert into it's own separate proc and put the whole thing in a DTS package and have it move to the next step regardless of success or failure.

Personally I have found it dangerous to not rollback everything if there is a failure becasue it can affect data integrity. If there should be a record first in the customer table and then in the customer address table and then in the customer phone table. and the address insert fails, then the whole customer should be rolled back because you don't have all the required data for that customer. If you keep going you may think all is fine with the customer until you try to send him his order.

"NOTHING is more important in a database than integrity." ESquared
 
The problem is that some errors will terminate the batch and some the connection, read the links SQLDenis posted
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top