RETURN @@ERROR?  
Author Message
Melikahiwa!





PostPosted: Wed May 10 15:06:31 CDT 2006 Top

SQL Server Developer >> RETURN @@ERROR?

If I execute one SP within a parent SP and want to trap and return an error



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




CREATE PROCEDURE childSP
@value int



SQL Server19  
 
 
JeffB





PostPosted: Wed May 10 15:06:31 CDT 2006 Top

SQL Server Developer >> RETURN @@ERROR? 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






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








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





goto ErrH




goto ErrH

ErrH:


 
 
Don





PostPosted: Wed May 10 15:48:16 CDT 2006 Top

SQL Server Developer >> RETURN @@ERROR? 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


I'm going over my SPs now to make sure I have one exit point instead of
exits all over.




> Your method will work for the simplest procedure but in the following 0
> will be returned if the first insert fails:
>
> CREATE PROCEDURE childSP

> as



>


> 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

> as





>


> 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

> as

>



> goto ErrH
>



> goto ErrH
>
> ErrH:

>


 
 
JeffB





PostPosted: Wed May 10 16:13:19 CDT 2006 Top

SQL Server Developer >> RETURN @@ERROR? If you don't like GOTO then the following also works:

CREATE PROCEDURE childSP
@value int
as








END