I am having a parent stored procedure in SQL Server 2000 which calls a number of procedures inside. I am getting an error due to some inaccurate data in few of the inline sps.
How can I trap the sp name which is showing the error. I tried using @@Error but it can be given only oin the parent sp which is executing the other sps. Control breaks in one of the child sps and @@Error in the parent sp is 0 itself.
How to capture this error?
There are many stored procured inside and its very difficult to put @@Error for each and every statement of those sps.
We check @@ERROR after every statement and set a local variable @intRetVal to a unique number, within the SProc, indicating where the error occurred.
We return @intRetVal from the SProc - although it may be better to return it as an OUTPUT parameter.
After an EXEC MyChildSproc we check both the return value and @@ERROR - @@ERROR may contain a value if the child SProc was aborted, e.g. if it sets XACT_ABORT ON
(SET XACT_ABORT ON is a useful thing to help prevent errors being terminal, but it is then important that the "caller" checks @@ERROR after EXEC-ing the child SProc)
I don't think there is a choice I'm afraid - its just part of the job. If you move from SQL2000 to a newer version you can use TRY / CATCH instead, which may mean you can wrap all the logic within the SProc in a single TRY / CATCH, which would be less work, but you may then find you do not have enough granularity in your error checking for it to be effective.
answered Nov 25, 2009 at 08:53 AM