On 18-7-2014 21:03, 'checkmail' check_mail@... [firebird-support]
wrote:

> Hello Mark,
>
> Here the code of the sub-Procedure:
>
> create or alter procedure P_U_CN_TO_INT (
> CN_IN varchar(30))
> returns (
> INT_OUT integer,
> STATUS smallint)
> AS
> declare variable I_ISNUMERIC smallint;
> BEGIN
> select true_param from p_u_isnumeric(:cn_in) into :i_isnumeric;
> if(i_isnumeric = 1) then
> begin
> int_out = cast(cn_in as integer);
> status = 1;
> end
> else
> begin
> int_out = 0;
> status = 0;
> if(cn_in is null) then cn_in = 'NULL!';
> execute procedure p_db_ins_error('P_U_CN_TO_INT','CN_IN: '|| :cn_in,11);
> end
> SUSPEND;
> when any do
> begin
> execute procedure p_db_ins_error('P_U_CN_TO_INT','CN_IN: '|| :cn_in || '
> SQL-Fehler: ' || sqlcode ,10);
> status = 0;
> int_out = 0;
> suspend;
> end
> END
>
> If I call it with '1234', I get 1234 as integer. For example 'AB12' - and I
> get as status 0 an not an integer value.
>
> Dat is a string with many digits. I would spit it. When I do it with:
> s_digits = substring(:dat from 1 for 4); and give the stored (sub) procedure
> the :s_digits for input parameter, it works. Otherwise I give the stored
> (sub) procedure the substring... directly, I get a sql-error -802. But it is
> the same content in the variable. And then I get not an integer value, for
> example 1234, it ends with this error.

It looks like you register the actual value of cn_in before storing it,
what is the problematic value. Have you checked if that is actually the
value that you exepected? You might also want to consider removing the
error handling from your stored procedure to check out if the error
message contains more information.

You don't show p_u_isnumeric, have you checked if the error occurs there?

Mark
--
Mark Rotteveel