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

I wrote two tiny stored procedures without noticing the problem you are mentioning, that is, one stored procedure contained a substring when calling the other stored procedure. However, it was very basic procedures (no tables involved, nor conversion to numbers), so it could just be that my test doesn't cover your situation. What Firebird version are you using and how are you determining there is an error (e.g. I know that IB Expert and Hopper has some kind of stored procedure emulation, and errors in emulating Firebird is not the same as an error in Firebird)? Do you get the same error when using isql, IB_SQL or FlameRobin?

Set