Hello

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 an conversation problem. If I call the stored procedure with the input parameter (substring(:var from 1 for 4) it doesn’t work, but If I call it with (cast(substring(:var from 1 for 4)as varchar(20)) it does work! The var is a char variable, the input var from the stored procedure an varchar – can it be the problem?

Thanks a lot.