--- In firebird-support@yahoogroups.com, "lutterot" <lutteroth@...> wrote:

>
> Hi and thanks for the reply!
>
> > >I know that such a functionality could be achieved by writing a

stored

> > >procedure. But it is not possible to use stored procedures that

return

> > >scalars in the same manner as functions, right?
> >
> > Well, it is possible. You can write an executable SP that returns a
> single output field. It's not a function, though it really depends
> how you want to use it.
> >
> > But the normal way to return a scalar field in a set is simply to
> use a correlated subquery expression in your dynamic query.
> >
> > Provide an example of what you want to achieve so that people can
> share some of their favourite tricks with you. :-)
>
> I need something along these lines:
>
> CREATE TABLE substitution (x integer, y integer);
>
> CREATE PROCEDURE substitute (x integer)
> RETURNS (y integer)
> AS BEGIN
> SELECT y FROM substitution WHERE x=:x INTO :y;
> SUSPEND;
> END;
>
> Because the procedure uses a substitution table, the way the numbers
> are substituted can be changed easily in the DB. That's what I need.
> And I was wondering if there is an easy way to use it similar to this:
>
> CREATE TABLE somenumbers (n integer);
>
> INSERT INTO somenumbers VALUES (substitute(1));
>

Hello Christof,

This should be reasonably straight forward then, because you already
have a selectable stored procedure that returns exactly one row.

INSERT INTO somenumbers (n) select y from substiture(1);

Adam