Being able to do what your asking about can get quite complex. While I am
going to include some example code below, I would suggest you get your
hands on the latest edition of a book called

DB2® SQL PL: Essential Guide for DB2® UDB on Linux?, UNIX®, Windows?,
i5/OS?, and z/OS®, 2nd Edition BY: Zamil Janmohamed, Clara Liu, Drew
Bradstock,
Raul F. Chong, Michael Gao, Fraser McArthur, and Paul Yip
ISBN: 0-13-147700-5
Publisher: Prentice Hall PTR
Copyright: 2005
Format: Cloth Bound w/CD-ROM; 576 pp
Published: 11/15/2004

In your calling procedure, you need to declare variables to hold the error
values that will be returned from the called procedure. Something like:

--*
--*** DECLARE VARIABLES FOR CALLED PROCEDURES
--*
DECLARE V_EVSP003E_SQLCODE INTEGER DEFAULT 0;
DECLARE V_EVSP003E_SQLSTATE VARCHAR(005) DEFAULT '00000';
DECLARE V_EVSP003E_ERROR VARCHAR(4096) DEFAULT NULL;
DECLARE V_EVSP003E_RESULT VARCHAR(4096) DEFAULT NULL;

In the called procedure, you will need to define the output variable that
will be used to return error information to the calling procedure.

CREATE PROCEDURE EVSP003E
(
OUT O_SQLCODE INTEGER,
OUT O_SQLSTATE CHARACTER(5),
OUT O_ERROR VARCHAR(4096),
OUT O_RESULT VARCHAR(4096)
)


In the called procedure, you will need an exit handler in your
declarations that will return the error to the calling procedure. In the
example, below, I am using a table to log all errors into so that I have
some specific application level information to help debug the problem.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 O_ERROR = MESSAGE_TEXT;
VALUES (SQLSTATE, SQLCODE) INTO O_SQLSTATE,O_SQLCODE;
SET O_RESULT = 'PROCEDURE NAME: ' || RTRIM(V_ERROR_PROCEDURE) ||
' - '
|| 'ERROR PARAGRAPH: ' || RTRIM(V_ERROR_PARAGRAPH) ||
' - '
|| 'ERROR STATEMENT: ' || RTRIM(V_ERROR_STATEMENT);
INSERT INTO EV_URG_LOG (COMMENTS)
VALUES '*** PROCEDURE ' || RTRIM(V_ERROR_PROCEDURE) || '
ABNORMALLY TERMINATED ***' ;
INSERT INTO EV_URG_LOG (COMMENTS)
VALUES ' E/V/C/S = '
|| RTRIM(CHAR(V_EVENT_KEY))
|| '/' || RTRIM(CHAR(V_COMPONENT_KEY))
|| '/' || RTRIM(CHAR(V_VERSION_KEY))
|| '/' || RTRIM(CHAR(V_STORE_NO));
INSERT INTO EV_URG_LOG (COMMENTS)
VALUES ' PARAGRAPH = ' || RTRIM(V_ERROR_PARAGRAPH)
|| ' *** STATEMENT = ' || RTRIM(V_ERROR_STATEMENT);
INSERT INTO EV_URG_LOG (COMMENTS)
VALUES ' ERROR = ' || O_ERROR;
COMMIT;
END;

In your calling procedure, you will need to issue the call and then make
sure to include logic to check for a good execution of the called
procedure.

CALL EVSP003E
(
,V_EVSP003E_SQLCODE
,V_EVSP003E_SQLSTATE
,V_EVSP003E_ERROR
,V_EVSP003E_RESULT
);

GET DIAGNOSTICS V_RETURN_VALUE = RETURN_STATUS;

IF V_RETURN_VALUE < 0
THEN SIGNAL c_apperr SET MESSAGE_TEXT =
'*** SEE TABLE EV_URG_LOG FOR ERROR MESSAGES ***';
ELSEIF V_EVSP003E_SQLSTATE <> '00000'
THEN SET O_SQLCODE = V_EVSP003E_SQLCODE;
SET O_SQLSTATE = V_EVSP003E_SQLSTATE;
SET O_ERROR = V_EVSP003E_ERROR;
SET O_RESULT = V_EVSP003E_RESULT;
SIGNAL c_apperr SET MESSAGE_TEXT =
'*** SEE TABLE EV_URG_LOG FOR ERROR MESSAGES ***';
END IF;



Al Glinter ? Lead Database Administrator ? I.T. Solution Center
Lord & Taylor ? 500 North Broadway, Suite 1250 ? Saint Louis, MO 63102
phone (314) 552-3752 email [Email address protected]
www.lordandtaylor.com



"vedant k7 " <db2udbdba-ezmlmshield-x24852846.[Email address protected]
11/14/2007 01:43 PM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
question on sql exception handling...







In a Nested stored procedure call, how do I append the name of the stored
to the SQL error message text going back to the application tier? I know
there is a restriction of 70chars for Error message text. I just need
raise the error from my inner most call. Thanks, Surendar.
_________________________________________________________________
Windows Live Spaces is here! It?s easy to create your own personal Web
site.
http://spaces.live.com/?mkt=en-in

---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DB2 & UDB email list listserv db2-l LazyDBA home page