> BEGIN
> EXCEPTION_DATA = 'LYDCBKJ;'||CAST(:ABKJ AS VARCHAR(9));
> EXCEPTION INVALID_ENTRY;
> /* EXIT; */ <---------- doesn't work
> /* SUSPEND; */ <---------- doesn't work
> END

Of course, the Begin/End block is left when you raise
the EXCEPTION, so neither EXIT nor SUSPEND is never executed.

> BEGIN
> EXCEPTION_DATA = NULL;
> EXECUTE PROCEDURE ACCEPT_DOCUMENT_ID
> (:ENDDATE,:BOEKJAAR,:USERID,:LANGUAGE,:REKKLANT,:REKLEVER,:AID)
> RETURNING_VALUES :EXCEPTION_DATA; <----------
> WHEN EXCEPTION INVALID_ENTRY DO
> BEGIN
> ERRORCOUNT = ERRORCOUNT + 1;
> INSERT INTO TMPLOG
> (TMPLOSID,TMPLOTID,TMPLOUSERNAME,TMPLOEXCEPTION_NAME,TMPLOEXCEPTION_MS
> G)
> VALUES
> (:SESSIONID,:AID,USER,'INVALID_ENTRY',:EXCEPTION_DATA); <----------
> END

And because the inner procedure raised the exception, its return values
are not defined (more precisely not assigned to output parameters)


Ivan

----- Original Message -----
From: "flipmooooo" <flipmooooo@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, February 11, 2005 5:21 PM
Subject: [firebird-support] Stored procedure return_value always null


>
>
> Hi,
>
> Sorry for bothering u guys againbut i'm having some problems again
> with my 'ever lasting' first SP. I have 2 SP's. In the main SP i call
> the 2nd one. If in the 2nd SP a 'INVALID_ENTRY' exception occurs
> EXCEPTION_DATA is filled with fieldinfo and passed back to the main
> SP. But after returning to my main SP EXCEPTION_DATA is always NULL.
> I tried putting 'EXIT' after 'EXCEPTION INVALID_ENTRY' but that
> doesn't work either. Tried 'SUSPEND' too but since its a executable
> procedure it shouldn't be used either according to the language
> reference guide, exit should be used instead. Anyway 'SUSPEND' didn't
> work either.
>
> Greetings,
> Filip Moons
>
> CREATE PROCEDURE ACCEPT_DOCUMENT_DOC (
> SESSIONID INTEGER,
> ENDDATE DATE,
> BOEKJAAR INTEGER,
> USERID INTEGER,
> LANGUAGE INTEGER,
> REKKLANT VARCHAR(10),
> REKLEVER VARCHAR(10))
> RETURNS (
> ERRORCOUNT INTEGER)
> AS
> DECLARE VARIABLE AID INTEGER;
> DECLARE VARIABLE EXCEPTION_DATA VARCHAR(78);
> begin
> ERRORCOUNT = 0;
>
> FOR
> SELECT LYDCID
> FROM LYDOC
> WHERE
> LYDCBKJ = :BOEKJAAR AND
> LYDCACCEPTED = 0
> ORDER BY LYDCDGB,LYDCDOCNR
> INTO :AID
> DO
> BEGIN
> EXCEPTION_DATA = NULL;
> EXECUTE PROCEDURE ACCEPT_DOCUMENT_ID
> (:ENDDATE,:BOEKJAAR,:USERID,:LANGUAGE,:REKKLANT,:REKLEVER,:AID)
> RETURNING_VALUES :EXCEPTION_DATA; <----------
> WHEN EXCEPTION INVALID_ENTRY DO
> BEGIN
> ERRORCOUNT = ERRORCOUNT + 1;
> INSERT INTO TMPLOG
> (TMPLOSID,TMPLOTID,TMPLOUSERNAME,TMPLOEXCEPTION_NAME,TMPLOEXCEPTION_MS
> G)
> VALUES
> (:SESSIONID,:AID,USER,'INVALID_ENTRY',:EXCEPTION_DATA); <----------
> END
> WHEN GDSCODE DeadLock, GDSCODE lock_conflict DO
> BEGIN
> ERRORCOUNT = ERRORCOUNT + 1;
> INSERT INTO TMPLOG
> (TMPLOSID,TMPLOTID,TMPLOUSERNAME,TMPLOGDSERROR) VALUES
> (:SESSIONID,:AID,USER,GDSCODE);
> END
> END
> end
>
> CREATE PROCEDURE ACCEPT_DOCUMENT_ID (
> ENDDATE DATE,
> BOEKJAAR INTEGER,
> USERID INTEGER,
> LANGUAGE INTEGER,
> REKKLANT VARCHAR(10),
> REKLEVER VARCHAR(10),
> DOCUMENTID INTEGER)
> RETURNS (
> EXCEPTION_DATA VARCHAR(78))
> AS
> begin
> if (SOME_TEST_FAIL) then
> BEGIN
> EXCEPTION_DATA = 'LYDCBKJ;'||CAST(:ABKJ AS VARCHAR(9));
> EXCEPTION INVALID_ENTRY;
> /* EXIT; */ <---------- doesn't work
> /* SUSPEND; */ <---------- doesn't work
> END
> end