Hi Roger,

In order to return multiple rows, you need to call the procedure
via SELECT ... FROM procedurename(parameters)

What your procedure does is returning a single row - why?
Because SUSPEND signals the procedure to return rows -
however, your SUSPEND command is OUTSIDE the
FOR ... DO BEGIN ... END loop - so it performs the loop,
then returns the parameters because of the SUSPEND.

My guess is that moving the SUSPEND to inside the loop
would do the trick.


With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com

Firebird Workbench - the developer tool for Firebird
http://www.upscene.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> Could someone cast an eye over this SP below
> - I cannot get it to work properly
>
> The general idea is that the first SQL (the for loop)
> returns x rows (which in isolation it does) but when I add
> the second SQL, which determines how many of the measure I have
> it only returns the last row - or maybe I have it completely wrong
>
> Thanks
>
>
> Roger P
>
> --------------------------------------------------------------------------

--

> ------------
> create procedure gef_measures (GEF_BATCH integer)
> returns (GEF integer, REFNUM integer,
> MEASURE varchar(60), MEASURE_CODE varchar(10), REQUESTED varchar(1))
> as
> declare variable measurecount integer;
> declare variable measurecode varchar(10);
> begin
>
> for SELECT DISTINCT G.GRANT_FORM_NUMBER,

G.REFNUM,P.MEASURE_CODE,M.MEASURE

> FROM GRANTFORMS G, PRICE_MATRIX P,MASTER_MEASURE M
> WHERE
> (G.GRANT_BATCH = :gef_batch)
> AND (P.SCHEME_CODE = G.SCHEME_CODE)
> AND (P.MEASURE_CODE = M.MEASURE_CODE)
> ORDER BY G.GRANT_FORM_NUMBER, G.REFNUM, G.GRANT_BATCH,
> P.MEASURE_CODE
> into :gef,:refnum,:measure_code,:measure
>
> do
> begin
> requested = '';
> SELECT COUNT(DISTINCT MEASURE_CODE)
> FROM MEASURE_INITIAL
> WHERE (REF_NUM = :refnum)
> AND (MEASURE_CODE = :measure_code)
> into :measurecount;
>
> if (measurecount > 0) then
> requested = 'Y';
> end
> suspend;
> end
>
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>