Thanks for that Thomas.

I agree with your comments about the relevance of this example, I
just wanted to 'keep it simple' to focus on my issue which was the
call semantics in a select, which you answered most kindly :)

Regards,
Jono.

--- In firebird-support@yahoogroups.com, "Thomas Steinmaurer"
<ts@i...> wrote:

> > Does anyone know if it is possible to use in Firebird a stored
> > procedure like a UDF?
> >
> > For example, suppose I have defined a stored procedure like :
> >
> > create procedure concat (param1 varchar(50), param2 varchar(50))
> > returns (output_string varchar(100))
> > as
> > begin
> > for select (:param1 || :param2) from rdb$database
> > into :output_string do suspend;
> > end ^
>
> Try:
>
> create procedure concat (param1 varchar(50), param2 varchar(50))
> returns (output_string varchar(100))
> as
> begin
> output_string = param1 || param2;
> suspend;
> end
>
>
> > I would like to be able to call it in a way similar to :
> >
> > select concat(contact.firstname, contact.surname) contact_name

from

> > contact;
> >
> > Any ideas? Thanks for your time in advance.
>
> The call of the stored procedure has to be within an "Inline-

Select".

>
> For example:
>
> select
> (select output_string from concat (c.firstname, a.surname)) as

contact_name

> from
> contact c
>
>
> Btw, in your case, you don't need a stored procedure for that. A

simple

>
> select
> (c.firstname || c.surname) as contact_name
> from
> contact c
>
>
> will do the job as well. A stored procedure might make more sense,

if e.g.

> one of the parameters can be null, because you can put the logic

into the

> stored procedure to check, whether a parameter is null or not. If

it is

> null, then you should assign an empty string, otherwise the

concatenation

> will result in null as well.
>
> If you are using Firebird 1.5, then you don't need that check

neither,

> because you can use COALESCE(...).
>
>
> HTH,
> Thomas Steinmaurer
> LogManager Serie - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database and MS SQL Server
> Upscene Productions
> http://www.upscene.com