Hi Paul,

> >> ...shouldn't you place the second apostrophe at the end of the
> >> statement to be executed, i.e.
> >>
> >> execute statement
> >> 'select numf1 from my_table where id=1 into :my_numf';
>
> > No, this isn't right. I've taken the below statement from a
> > procedure that I used, I had to use it like this, because
> >"SUBSTRING" doesn't accept an expressesion/variable, but only
> > a constant:
> >
> > execute statement 'select substring(email from 1 for ' || i || ')
> > from gebruiker where gebruikerid = ' || gebruikerid
> > into :prefix;
>
> Thanks - I wasn't aware that you could execute a statement INTO
> something. But indeed I just found this note in the WhatsNew doc,
> under v1.5 beta 4:
>
> * Improved EXECUTE STATEMENT.
> Now it's possible to return values from the dynamic SQL.
> Syntax:
> EXECUTE STATEMENT <value> INTO <var_list>; (singleton form)
> or
> FOR EXECUTE STATEMENT <value> INTO <var_list> DO <stmt_list>;
> Contributor(s):
> Alexander Peshkoff <peshkoff at hotmail.ru>
>
>
> I suppose however that "my" version should work too, because it's
> a valid statement.

I wonder, though, if a local variable (my_numf) can be addressed from
within the code that executes "EXECUTE STATEMENT".

Well, just tried it:
SET TERM ^^ ;
CREATE PROCEDURE P_TEST returns (
MYVAR Integer)
AS
begin
execute statement 'select count(*) from rdb$database into :myvar';
end
^^
SET TERM ; ^^

And it fails when executing...


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com