x

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.

more ▼

asked Nov 25, 2009 at 08:11 AM in Default

SmarterTech.Info gravatar image

SmarterTech.Info
3 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 25, 2009 at 08:53 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x415
x21

asked: Nov 25, 2009 at 08:11 AM

Seen: 1317 times

Last Updated: Nov 25, 2009 at 08:54 AM