Stored procedure "Multiple rows in singleton select" error

ekehat
ekehat used Ask the Experts™
on
I wrote this procedure to look for users within an certein age range,
however, when I run it I get the error message: "Multiple rows in singleto
select".
Why is my select considered a singleton, and what can I do to get multiple
rows back?

(I'm running Firbird 1.0)

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 ; !!


Thanks,
Elad
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I've found the solution...

A for.. do with a SUSPEND in the end was it it took.

Elad
Interbase does not support multiple rowsets from a stored procedure.  A singleton select means you can only select a single row from a stored procedure.  This is just an interbase "feature".  Your operation generates multiple rows and since interbase only allows one row (singleton) from a stored procedure, your operation is illegal.

Unfortunately, the way around this is using either views or placing the data access code in the client application.
Commented:
Hi.

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 ; !!

By.
kacorretired

Commented:
Hi ehehat,

if you got the needed answer, please accept it by clicking on Accept in the header of the good answer. By this way you can express thanks for expert's support

with best regards

Janos
kacorretired

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:
       to split points as follows:
                 50 points to refund for ekehat
                 50 points for lucika                        for statement SUSPEND
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

kacor
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial