RAISERROR messages in nested Stored procedures

Question

RAISERROR messages in nested Stored procedures

Digs

SSCertifiable

Points: 7333
More actions
March 29, 2005 at 12:22 pm

#166522

I have one SP nested in another SP, the nested SP has a RAISERROR error message.

Like this…

CREATE PROCEDURE ONE @Input varChar(100)
DECLARE @ErrNo int
DECLARE @ErrMessage varChar(100)

….A instert statement with WHERE Data = @Input…
SET @ErrNo = @@Error
If @ErrNo > 0
BEGIN
            SET @ErrMessage = ‘Error insterting data Step 1’
            GOTO MYERROR
END

….More TSQL insert statements
SET @ErrNo = @@Error
If @ErrNo > 0
BEGIN
            SET @ErrMessage = ‘Error insterting data Step 2’
            GOTO MYERROR
END

RETURN 0

MYERROR:
BEGIN
            RAISERROR (@ErrMessage,1,1)
            RETURN @ErrNo
END
GO

CREATE PROCEDURE TWO
DECLARE @rc int
DECLARE @ErrNo int
DECLARE @ErrMessage varChar(100)

/* Nested stored Procedure with RAISERROR inside it, Green is input variable made up for example*/
EXEC @rc = ONE ‘Green’
SET @ErrNo = @rc
If @ErrNo > 0
BEGIN
            SET @ErrMessage = ???????
            GOTO MYERROR
END

RETURN 0

MYERROR:
BEGIN
            RAISERROR (@ErrMessage,1,1)
            RETURN @ErrNo
END
GO

QUESTION : How does one handle nested RAISERROR messages ine procedure two from procedure one. The procedure two will be run by the client, so how does one get messages to client from procedure one ???

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ron k SSCertifiable Points: 6103 More actions · Answer 1

Raiserror always returns its message directly to the client. So in this case an error in procedure ONE has already sent the error message to the client and Procedure TWO only needs to detect an error has occured and clean up and terminate processing by RETURNing.

Check out this link http://www.sommarskog.se/error-handling-II.html

it gives a lot of detail on error handling in stored procedures.

Digs SSCertifiable Points: 7333 More actions · Answer 2

So this code would be, is this correct

EXEC @rc = ONE ‘Green’

SET @ErrNo = @rc

If @ErrNo > 0

BEGIN

RETURN @ErrNo

END

ron k SSCertifiable Points: 6103 More actions · Answer 3

Yes, something like that but please review the link posted above. There are many other issues that must be addressed and this article does a great job of explaining everything.

RAISERROR messages in nested Stored procedures

Cookies on SQLServerCentral