Subject Re: [firebird-support] Re: Returning values from stored procedure
Author Paul Vinkenoog
Hi Mahesh,

> What is really bugging me is that apart from the cursor (which I'm
> handling thru Suspend, & it is working fine also) I wanted some
> other singleton values from my stored procedure. Otherwise I will
> have to change my code to call 2 stored procedures : one of the
> cursor, & another for other singleton values.

Or add the two singleton values as extra output fields to the
selectable SP. This is ugly however, because they then wind up
identically in every row of the output set. But it does save you from
having to use 2 SP's.

Another thing. Helen wrote:

> create procedure dosomething (<input-list)
> /* __EITHER__ make a returns-list if you want to output a set */
> returns (var1 sometype, var2 othertype, var3 anothertype, var5 sometype)
> AS
> /* __OR__ declare local variables to execute STUF on the server */
> declare variable var1 sometype;
> declare variable var2 othertype;
> ...etc.....

This surprised me because it looked as if she said categorically you
have to choose between a returns-list and local variables. I don't
think she really meant that (maybe the caps made it look extra heavy
;-)), but just to make sure you don't get the wrong impression:

You always declare a returns-list if you want to return something to
the caller. You do this both
- in a selectable SP, where you use SUSPEND and the caller uses SELECT;
- and in an executable SP, where the caller retrieves the output using

Regardless of the type of SP (selectable/executable) you can also
declare local variables as you see fit. These can not be accessed by
the caller of course.

Paul Vinkenoog