error handling in sql server 2000

[washaw]

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

 

[timfoster]

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.

 

[SQLSister]

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

 

[SQLDenis]

read and learn

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[LessThanDot]

The problem is that some errors will terminate the batch and some the connection, read the links SQLDenis posted