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.

Thanks in Advance.

Best regards

Olaf

-----Ursprüngliche Nachricht-----
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Gesendet: Freitag, 18. Juli 2014 14:33
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] substring

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

> If I use the following code to extract a part of a string, it works fine:
>
> s_datum = substring(:dat from 13 for 4); s_datum = char(4)
>
> Now I would give the same a stored procedure, the first input
> parameter is XXX varchar(30), it returns me the integer-value and the
> status (passed or not)
>
> select int_out, status from P_u_cn_to_int(substring(:dat from 13 for
> 4)) into :i_z1, i_status1;
>
> The second thing – it does not work – why? If I set the
> input-parameter to ‘1234’, it works, but the substring?

"It does not work" is a bit vague. What is the value of dat, and what is the
result of the substring? Does it match the expectations of the stored
procedure?

Mark
--
Mark Rotteveel


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu
there.

Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links