> 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