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 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 removi-ng 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
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.
