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
>

Handling Dataset in Stored Proc.

Hi,

I want to create and populate a dataset from store procedure with following to querires & return the dataset as a result.

Select * from billmain where billno = 12

Select * from billdetails where billno = 12

I am currently performing this task aa a resultset. Now I want to use Dataset. Anybody can send me sample sp which returns dataset as a execution of the sp.

Nilkanth Desai

A simple select statement would do. Call the stored procedure from your ADO.NET code and store the results of this stored procedure in a DataSet object.

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName FROM Table WHERE billno=@.billNo

|||

Hi,

Thanks fpr your Reply. This is working fine when I call it from my ADO.NET Code. But If I want to trf. Dataset as a return result of the said stored proc. where I will be doing multiple select in more then one table. So if I call it only once I can complete all task in a single call. In addition I want to marshal the job to server. So, In this case now tell me what should I do.I don't want to use CLR-Integrated stored procedure. In this case can How can I handle Dataset in above mentioned manner in a standard T-SQL stored procedure?

Nilkanth Desai

|||

You can return more than one SQL Server resultsets from a single stored procedure, see the example below.

Chris

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName

FROM BillMain WHERE billno=@.billNo

SELECT columnName

FROM BillDetails WHERE billno=@.billNo

GO