Error Handling in SQL?

Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LX
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>