Handling Errors

Is it possible to handle an FK violation error in SQL Server 2000?
IOW, if I encounter this error, is it possible for the code in my proc to
execute alternative code rather than immediately exiting with an error
message?
I can write code to always test for a FK violation before I try to insert,
but is it possible to do the same thing with error handling (i.e., try the
insert straight off and then if I get an error, do something else)?
I believe I can do this with the TRY/CATCH in SQL Server 2005 but I was
wondering if it is possible with the more limited error handling of SQL
Server 2000.
DaveHi, Dave
Yes, it is possible in a stored procedure, but not if the code is in a
trigger or called from a trigger. For example:
USE Northwind
INSERT INTO Territories
(TerritoryID, TerritoryDescription, RegionID)
VALUES (10000, 'Mars', 7)
IF @.@.ERROR<>0 BEGIN
INSERT INTO Region
(RegionID, RegionDescription)
VALUES (7, 'Outer space')
INSERT INTO Territories
(TerritoryID, TerritoryDescription, RegionID)
VALUES (10000, 'Mars', 7)
END
DELETE Territories WHERE RegionID=7
DELETE Region WHERE RegionID=7
However, it's recommended that you check for the FK violation before
the insert, because if you call this from ADO, it is difficult to
handle the error from the client side (it will still be raised on the
client and you don't know if it was handled by the procedure or if it
is a real error that should be logged or reported to the user).
You should also know that there are some errors that cannot be handled
this way in the procedure, because SQL Server aborts the batch when it
encounters errors like conversion errors, for example.
For more informations, see this excellent article by Erland Sommarskog,
SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html
Razvan|||Thank you very much Razvan!
That is indeed an excellent article by Erland that you refernce.
Dave
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116564456.658264.99430@.g44g2000cwa.googlegroups.com...
> Hi, Dave
> Yes, it is possible in a stored procedure, but not if the code is in a
> trigger or called from a trigger. For example:
> USE Northwind
> INSERT INTO Territories
> (TerritoryID, TerritoryDescription, RegionID)
> VALUES (10000, 'Mars', 7)
> IF @.@.ERROR<>0 BEGIN
> INSERT INTO Region
> (RegionID, RegionDescription)
> VALUES (7, 'Outer space')
> INSERT INTO Territories
> (TerritoryID, TerritoryDescription, RegionID)
> VALUES (10000, 'Mars', 7)
> END
> DELETE Territories WHERE RegionID=7
> DELETE Region WHERE RegionID=7
> However, it's recommended that you check for the FK violation before
> the insert, because if you call this from ADO, it is difficult to
> handle the error from the client side (it will still be raised on the
> client and you don't know if it was handled by the procedure or if it
> is a real error that should be logged or reported to the user).
> You should also know that there are some errors that cannot be handled
> this way in the procedure, because SQL Server aborts the batch when it
> encounters errors like conversion errors, for example.
> For more informations, see this excellent article by Erland Sommarskog,
> SQL Server MVP:
> http://www.sommarskog.se/error-handling-I.html
> Razvan
>|||The "set xact_abort on / off" setting determines whether some exceptions
immediately abort the transaction or continue processing with @.@.error.
"Dave" <dave@.nospam.ru> wrote in message
news:OvTcy4MXFHA.1148@.tk2msftngp13.phx.gbl...
> Is it possible to handle an FK violation error in SQL Server 2000?
> IOW, if I encounter this error, is it possible for the code in my proc to
> execute alternative code rather than immediately exiting with an error
> message?
> I can write code to always test for a FK violation before I try to insert,
> but is it possible to do the same thing with error handling (i.e., try the
> insert straight off and then if I get an error, do something else)?
> I believe I can do this with the TRY/CATCH in SQL Server 2005 but I was
> wondering if it is possible with the more limited error handling of SQL
> Server 2000.
> Dave
>