Thanks for the replay

How to I catch the error message
'#ProcB: Bummer! Error in Procedure B' in stored procedure
#ProcA?

Hi Bob,

Normally, when programming stored procedures, we use the RETURN code to =
pass logic or business rule errors -we typically don't use RAISERROR.

I've altered the previous sample code to demonstrate passing a business =
rule error from ProcB back out to ProcA, and having ProcA make decisions =
based upon the knowledge that ProcB had a 'problem'.

When using the RETURN value of a stored procedure, zero [0] implies =
success, any non zero return value indicates some form of 'problem'. =
While you can arbitrarily choose your own numbers, it is best to have a =
'standard' in place when determining what RETURN values to use.

Obviously, there can be many reasons to abort processing a stored =
procedure, or even to report different levels of 'success'. So there can =
be many places in a stored procedure where there is a RETURN (which =
aborts any further code processing in the stored procedure and returns =
to the calling code.

For an 'in-depth look as stored procedure error handling, see Erland's =
excellent articles:

Stored Procedure -Error Handling, Background
http://www.sommarskog.se/error-handling-I.html=20

Stored Procedure -Error Handling, Implementation
http://www.sommarskog.se/error-handling-II.html=20


CREATE PROCEDURE #ProcB
( @B_Switch int )
AS
IF @B_Switch =3D 1
BEGIN
--Business Rule Error in Procedure B
RETURN 50000
END =20
ELSE
BEGIN
-- No ERROR in Procedure B
RETURN 0
END
GO
=20
CREATE PROCEDURE #ProcA
( @A_Switch int )
AS=20
DECLARE=20
@Err int,
@Return int
EXECUTE @Return =3D #ProcB @B_Switch =3D @A_Switch
IF ( @@ERROR <> 0 ) OR ( @Return > 0 )
BEGIN
PRINT '#ProcA: Procedure B reported an ERROR'
PRINT 'Error Number: ' + cast( @Return AS varchar(10) )
END
ELSE=20
Print '#ProcA: No Error'
GO
=20
PRINT '********************'
EXECUTE #ProcA 1
PRINT '********************'
EXECUTE #ProcA 2
PRINT '********************'

DROP PROCEDURE #ProcA
DROP PROCEDURE #ProcB

--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous

You can't help someone get up a hill without getting a little closer to =
the top yourself.
- H. Norman Schwarzkopf


[quoted text, click to view]