RETURN @@ERROR?

If I execute one SP within a parent SP and want to trap and return an error
code from the child SP, can I just use RETURN @.@.ERROR in the child SP rather
than capturing @.@.ERROR in a local variable first? Or does the successful
completion of the RETURN statement set @.@.ERROR back to 0 by the time it gets
back to the parent SP?
The reason I'm asking is that all the examples I've found about error
trapping don't seem to do it this way, and this way seems the most
straightforward. Thanks for any insight.
CREATE PROCEDURE parentSP
DECLARE @.returncode int
EXEC @.returncode = childSP @.value
IF @.returncode <> 0 Do something like rollback transaction
CREATE PROCEDURE childSP
@.value int
INSERT INTO someTable (column) VALUES (@.value)
RETURN @.@.ERRORYour method will work for the simplest procedure but in the following 0
will be returned if the first insert fails:
CREATE PROCEDURE childSP
@.value int
as
INSERT INTO someTable (column) VALUES (@.value)
IF @.@.ERROR <> 0
RETURN @.@.ERROR
INSERT INTO someOtherTable (column) VALUES (@.value)
RETURN @.@.ERROR
go
It would have to be, a minium the following but this introduces
different methods of handling errors and provides multiple exit points
for the procedured:
CREATE PROCEDURE childSP
@.value int
as
declare @.err
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
RETURN @.err
INSERT INTO someOtherTable (column) VALUES (@.value)
RETURN @.@.ERROR
go
Best practices of coding state that each object should only have 1 exit
point so the following is the standard that I use:
CREATE PROCEDURE childSP
@.value int
as
declare @.err int
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
goto ErrH
INSERT INTO someOtherTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err <> 0
goto ErrH
ErrH:
return @.ErrorSave|||Thanks. Yes, most of my "child" INSERT, UPDATE SPs are single statements and
I didn't have them trap errors or explicitly RETURN codes and just thought
RETURN @.ERROR was the most expedient.
I'm going over my SPs now to make sure I have one exit point instead of
exits all over.
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1147291591.811293.125770@.y43g2000cwc.googlegroups.com...
> Your method will work for the simplest procedure but in the following 0
> will be returned if the first insert fails:
> CREATE PROCEDURE childSP
> @.value int
> as
> INSERT INTO someTable (column) VALUES (@.value)
> IF @.@.ERROR <> 0
> RETURN @.@.ERROR
> INSERT INTO someOtherTable (column) VALUES (@.value)
> RETURN @.@.ERROR
> go
> It would have to be, a minium the following but this introduces
> different methods of handling errors and provides multiple exit points
> for the procedured:
> CREATE PROCEDURE childSP
> @.value int
> as
> declare @.err
> INSERT INTO someTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> RETURN @.err
> INSERT INTO someOtherTable (column) VALUES (@.value)
> RETURN @.@.ERROR
> go
> Best practices of coding state that each object should only have 1 exit
> point so the following is the standard that I use:
> CREATE PROCEDURE childSP
> @.value int
> as
> declare @.err int
> INSERT INTO someTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> goto ErrH
> INSERT INTO someOtherTable (column) VALUES (@.value)
> select @.err = @.@.ERROR
> IF @.err <> 0
> goto ErrH
> ErrH:
> return @.ErrorSave
>|||If you don't like GOTO then the following also works:
CREATE PROCEDURE childSP
@.value int
as
declare @.err int
INSERT INTO someTable (column) VALUES (@.value)
select @.err = @.@.ERROR
IF @.err = 0 BEGIN
INSERT INTO someOtherTable (column) VALUES (@.value)
select @.err = @.@.ERROR
END
return @.Err