At 03:26 AM 4/12/2010, you wrote:

>Hello all
>
>I have a question to usage and performance of stored procedures that return only one result row/value.
>Is there a performance drawback when I code it as a selectable procedure,

Code it as a selectable procedure if you plan to return a set of zero to n rows to the client.

> or should I use always the execute procedure syntax

Code it as an executable procedure using returning_val when
a) you want to return a set of zero or one rows to the client
or
b) you want to return values from a sub-procedure back to local variables in a calling procedure

> and drop the suspend?

Understand what SUSPEND actually *does*. It sends a result row out to the client buffer and waits for the client app to fetch the row. Execution of the SP is *suspended* until the waiting row has been fetched.

So use the features according to their intended use and life will be sweet.

>Is it correct that I can not assign directly to a variable as with a UDF?

I don't quite understand what you are asking here.

A UDF is a *function*: it takes one or more input parameters and returns one scalar result. Some UDFs have an implicit input, e.g., GetExplicitTimestamp() in the FbUDF library. A better name for UDF is External Function, because it is code that is executed externally, *not* compiled in either the engine or the database.

A stored procedure is...well...a procedure. It can take inputs (parameters) but it doesn't have to. It can call other stored procedures and it can be called by other stored procedures. It can return a result set but it doesn't have to. Many executable SPs just execute some data-changing operations. Furthermore, there are some good reasons why you shouldn't write SPs that both change data and return sets. A SP is stored in a database in an executable binary format known as BLR.

I think possibly your question means "Can you use a SP call in an expression in the same way as you can use a function call?" The answer to that is broadly "No", although you can write selectable SPs that return a scalar result from a subquery expression.

>create procedure MYPROC(
> IN_PORTION float)
>returns (
> HALF_PORTION float)
>as
>begin
> HALF_PORTION = IN_PORTION / 2;
> suspend;
>end
>
>usage as (SELECTABLE) STORED PROCEDURE:
>execute procedure MYPROC( PARAM) returning_values :RESULT

This is not usage as a selectable SP. The SUSPEND in this procedure, when invoked with EXECUTE PROCEDURE, is a no-op (actually, it just behaves as EXIT).

Your procedure, as written, will get you a scalar result back to the client, by simply selecting, viz.

select result from MYPROC(PARAM);

-- which could be used in a subquery, e.g.,
select
column1,
column2,
(select result from MYPROC(PARAM)) RESULT
from aTable;

>usage as SELECTABLE STORED PROCEDURE:
>select * from MYPROC( 19.0 ) into :RESULT;

Hmm, not a great idea. Sometimes it will work (as this one would, since it is scalar) but sometimes you will get unpleasant surprises. SSPs are for returning multi-row sets to a client or (if you must) returning a multi-row set to a cursor (fairly inefficiently).

>usage of a UDF
>:RESULT = udf_myFunc( 19.0 );

That is not a valid syntax. You can do something a bit like this in DSQL with a scalar SSP, viz.,

select udf_myFunc( 19.0 ) RESULT from rdb$database;

If you want a result from a UDF in PSQL (or, indeed, from a built-in function), you would do it like this:

create procedure blah (aInput numeric (18,1))
returns (result someType) as
begin
result = udf_myFunc (19.0);
suspend;
end

You are probably already familiar with how this works, if you have written any autoinc triggers for your tables and used the Gen_ID() function:

create trigger blah for aTable
as
begin
if (new.ID is null) then
new.ID = gen_ID (aGenerator, 1);
end

./heLen