Trapping for Error returned from Stored Procedure

[gradley]

I have a DTS package created in SQL 2000 that has a Execute SQL Task which in turn calls a Stored Procedure.

In the Stored Procedure, I'm trying to delete a record from a table that could have foreign keys constraints if the employee has transactional history. I am trapping the error in the stored procedure and returning an error number to the calling DTS step.

Even though I trap for this error, it still caused the DTS step to fail. Is there any way to clear the error being raised so that the DTS package continues processing?

Any help would be appreciated.

thanks,

Gordon Radley

 

[mrdenny]

Nope. Unfornitually SQL doesn't have ON ERROR RESUME NEXT or anything like that, yet. It is suppose to be in SQL 2005 (or what ever the next release is goig to be called).

What I would recommend is in your procedure check the child table, and if there is anything to delete then delete it. If not the go ahead and process the parent table.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gradley]

That's what I was afraid of. Bring on SQL Server 2005!

Thanks for the response.

Gordon

 

[mrdenny]

no problem.

Denny

--Anything is possible. All it takes is a little research. (Me)