Thanks, the SUSPEND was missing.
In fact, it IS a direct statement now, but since it appears x-times in a
SQL with lots of unions, I thought of putting it into a SP.
The calling context should be easier to maintain that way, and it should
speed up the execution, if I have well understood
the doc stuff about SP.

Thanks again
K.





Helen Borrie
<helebor@.... An: firebird-support@yahoogroups.com
au> Kopie:
Thema: Re: [firebird-support] Stored Procedure Output Parameter
25.11.2003 13:56
Bitte antworten
an
firebird-support
Telefon:







At 12:18 PM 25/11/2003 +0000, you wrote:

>Hi all,
>
>I am trying to use a SP in order to choose one of two strings,
>depending on the value of a numeric variable. All of them are passed
>as input parameters to the procedure.
>FB Version is 1.0.
>Following the advice in the FB Doc I wrote:
>----------------------------------------------------
>SET TERM ^^ ;
>CREATE PROCEDURE FORM2 (NVAL Double Precision, NDEC Double Precision,
>CTEXTSINGULAR VarChar(25) CHARACTER SET WIN1252, CTEXTPLURAL VarChar
>(25) CHARACTER SET WIN1252) returns (CRETSTRING VarChar(200)
>CHARACTER SET WIN1252) AS
>
>begin
>cretstring = cast(cast(:nval as varchar(20)) || cast(' ' as varchar
>(20)) || ... <UDF to choose the correct string-variable> ... as
>varchar(200));

SUSPEND;

>end
>^^
>SET TERM ; ^^
>--------------------------------------------------------
>In debug mode I can see, that the procedure works fine, since
>cretstring holds the correct string at the end.
>
>But there is no result returning to the calling statement:
>
>select * from form2(1,0,'String1','String2');
>
>Where is the error ?

You need a SUSPEND statement for a selectable stored procedure.

> Any alternatives ?

Since this is a singleton, it would work "as is" if you called it with
EXECUTE PROCEDURE and read the output parameters after execution.

You could also do this directly with a DSQL statement (no SP) as follows:

select cast((((all of that complicated stuff ))) as varchar(200) )
as CRETSTRING CHARACTER SET WIN1252
from RDB$DATABASE;

heLen




To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/