April 21, 2003 at 9:36 am
I want to trap the Error codes generated during Insert, Update, Delete in SP
in SQLSERVER 2000 and return the customer the custom error messages.
I can do this in PL/SQL tracking the following exceptions:
I want to track the list and more errors if possible. I understand the custom messages start from 50,0001.
Appreciate all your help in helping me track the error
April 24, 2003 at 8:00 am
This was removed by the editor as SPAM
April 24, 2003 at 8:53 am
One way to trap the error is to use the @@ERROR global variable in the stored procedure.
for eg.,
INSERT INTO tableA Values (1,2,3)
SET @m_err = @@ERROR
IF @m_err = 1202
print "Deadlock error."
IF @m_err = 547
print "A check constraint violation occurred"
You can find all the error messages in sysmessages table in master database from which you can find maching error numbers for the error messages required for you.
However note that if severity of error is higher, the stored procedure execution might abort before executing the IF condition that checks @@error.
April 24, 2003 at 8:54 am
I forgot to mention that to raise a custom error you can use RAISERROR statement
April 24, 2003 at 9:00 am
Within stored procedures, you can use:
return <numeric_value>
which you client app should be able to trap espically if you are using ADO.
You can put any numeric value for the return value which could be a specific error code.
Also, if you are using ADO, you should be able to use the Error Object to get error codes and messages. You can use raiserror to generate your own error codes and messages.
Hope this helps.
Jeremy
April 24, 2003 at 9:22 am
To add to rajeshpatavardhan, first check to see if @m_err <> 0, then start custom error checking.
Everett Wilson
ewilson10@yahoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply