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.
LXhttp://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
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
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
>