Catching errors within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---