question

SmarterTech.Info avatar image
SmarterTech.Info asked

Error Handling for Inline sp

Hi,

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.

sql-server-2000stored-procedureserror-handling
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered

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)

There are many stored procured inside and its very difficult to put @@Error for each and every statement of those sps.

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.