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.