trapping error in stored proc

trapping error in stored proc

Post by Toan » Fri, 13 Oct 2000 04:00:00



Hi all,

is it possible to trap errors in a stored proc in order
to insert the error message in a log table ?

User errors are rqised by raiserror function

but is there any instruction which enable to handle all errors
(both user defined and system messages)

Thanks

Toan

 
 
 

trapping error in stored proc

Post by Andrew J. Kell » Fri, 13 Oct 2000 04:00:00


Toan,

Essentially you need to test for an error condition after each prevalent
statement to determine if one happened or not. You do this by checking the

sql type statement or it will be reset back to 0. If the error is not severe
enough to cancel the stored proc altogether you can trap the error and
potentially exit a little more gracefully. Something like this:


UPDATE table SET xxx = 77887


    GOTO On_Error

INSERT INTO Table ( xxx) VALUES (77887)


    GOTO On_Error

RETURN 0

On_Error:

--
Andrew J. Kelly
Targitmail.com


Quote:> Hi all,

> is it possible to trap errors in a stored proc in order
> to insert the error message in a log table ?

> User errors are rqised by raiserror function

> but is there any instruction which enable to handle all errors
> (both user defined and system messages)

> Thanks

> Toan


 
 
 

1. howto trap error msg in stored proc

I have a stored proc which inserts to a table with FK constraints on it. If
the insert fails due to a FK problem (error 547) I need to trap the error
msg because it contains information, such as the column which caused the
problem and the name of the FK constraint, etc.

The table being inserted into can have as many as 19 foreign keys, so
testing each individually would be too time consuming.

Is there a global variable which holds the error msg text, or some other way
to trap this information?

Thanks in advance,

John C.

2. Independent Consultnats... Get Direct Clients with no middleman or broker

3. Error Trapping - Can you trap a DTS Package error using SQLDMO running a job

4. MAX(COUNT(*))?

5. Error Trapping: MS Access errors not trapped in VB code

6. Area code changes

7. Stored Proc Calling Another Stored Proc

8. How to get results from Stored Proc within a Stored Proc

9. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

10. calling stored proc from stored proc via variable

11. Calling a stored proc within a stored proc

12. Calling Stored Proc from other Stored Proc