🚀 go-pugleaf

See the descriptions of FOR SELECT  and SUSPEND in the Language
Reference to understand how to return multiple rows from a stored
procedure.

SET TERM !! ;
CREATE PROCEDURE SEARCH_USERS
 (MIN_AGE SMALLINT, MAX_AGE SMALLINT)
RETURNS (ID INTEGER)
AS
BEGIN
  FOR SELECT USER_ID
     FROM USERS
     WHERE CAST (EXTRACT (YEAR FROM CAST ('NOW' AS DATE)) AS SMALLINT)
-
     CAST (EXTRACT (YEAR FROM DATE_OF_BIRTH) AS SMALLINT)
  BETWEEN :MIN_AGE AND :MAX_AGE
     INTO :ID;
  DO
    SUSPEND;
END!!
SET TERM ; !!

On Wed, 1 Jan 2003 22:22:35 +0200, "Elad Keat" <elad@NOSPAM.COM>
wrote:

>SET TERM !! ;
>CREATE PROCEDURE SEARCH_USERS
> (MIN_AGE SMALLINT, MAX_AGE SMALLINT)
>RETURNS (ID INTEGER)
>AS
>BEGIN
>  SELECT USER_ID
>     FROM USERS
>     WHERE CAST (EXTRACT (YEAR FROM CAST ('NOW' AS DATE)) AS SMALLINT) -
>     CAST (EXTRACT (YEAR FROM DATE_OF_BIRTH) AS SMALLINT)
>  BETWEEN :MIN_AGE AND :MAX_AGE
>     INTO :ID;
>END!!
>SET TERM ; !!


--
Bill (TeamB)
(TeamB cannot respond to questions received via email)