Hi list,

I have a SP which will insert a record to the child table and its parent table. If the insert fails for any reason, the whole transaction will be rolled back and an user defined error message will be thrown out. e.g.

create procedure insert_category (....)
AS

decalre @errString varchar(200)
...
insert into category values (...)
IF @@ERROR<>0
BEGIN
SET @ErrString = 'Error insert record to Category table.'
GOTO ERREXIT
END
...

ERREXIT:
RAISERROR ('%s', 16, 1, @ErrString)
IF @@Trancount <> 0
ROLLBACK TRAN
RETURN (1)

GO

---
This SP works fine but now a developer like to catch the @ErrString and log it to the error log. At first I thought I can do it by using the output parameter. But now I realized that it won't work since as soon as an error occurs, the control will go to ErrExit and then the calling program -- the SP itself won't get a chance to return anything back except the error code (which is an interger value).

So how can he catch the @ErrString?

Thanks a lot,
Elaine








MS Sql Server LazyDBA home page