Resolving a singleton error in a stored procedure

Question

I have the following stored procedure in Firebird SQL:

ALTER PROCEDURE SP_REPORT_USD
(
  PER SMALLINT
)
RETURNS
(
  ACCOUNT_NUMBER CHAR(21),
  AMOUNT NUMERIC(15, 4)
  )

AS

BEGIN

SELECT
      L.ACCOUNT_NUMBER, SUM(CURRROUND(L.DEBIT,2)-CURRROUND(L.CREDIT,2))
   FROM
      LEDGER L
   WHERE
      L.LEDGER_ACCOUNT = '31621' AND L.PERIOD = :PER
   GROUP BY
      L.ACCOUNT_NUMBER

   INTO
      ACCOUNT_NUMBER, AMOUNT;

SUSPEND;

END

When I run the following query:

SELECT * FROM SP_REPORT_USD('17')

I get the following error:

MULTIPLE ROWS IN SINGLETON SELECT
AT PROCEDURE 'SP_REPORT_USD' LINE: 15, COL: 1

Line 15 Col 1 is where my select statement starts when doing the stored procedure.

I did test the following query:

SELECT
   L.ACCOUNT_NUMBER, INV.DESCRIPTION, SUM(-(CURRROUND(L.DEBIT,2) - CURRROUND(L.CREDIT,2)))
FROM
   LEDGER L join INVENTORY INV ON L.ACCOUNT_NUMBER = INV.STOCK_CODE
WHERE
   L.LEDGER_ACCOUNT = '31621' AND L.PERIOD = 17
GROUP BY
   L.ACCOUNT_NUMBER, INV.DESCRIPTION

And the results where as expected. So I know my query logic is correct, I am just doing something wrong with the stored procedure.

Any assistance will be appreciated.

Answer 1

The problem is that inside a stored procedure, a SELECT statement is for selecting values from a single row only (a so-called singleton select). Your query is producing multiple rows, hence the error "multiple rows in singleton select".

If you want to produce multiple rows, you need to use the FOR SELECT statement, and the SUSPEND statement must be in the body of this FOR SELECT statement:

ALTER PROCEDURE SP_REPORT_USD(PER SMALLINT)
  RETURNS (ACCOUNT_NUMBER CHAR(21), AMOUNT NUMERIC(15, 4))
AS
BEGIN
   FOR 
     SELECT L.ACCOUNT_NUMBER, SUM(CURRROUND(L.DEBIT,2)-CURRROUND(L.CREDIT,2))
     FROM LEDGER L
     WHERE L.LEDGER_ACCOUNT = '31621' AND L.PERIOD = :PER
     GROUP BY L.ACCOUNT_NUMBER
     INTO :ACCOUNT_NUMBER, :AMOUNT 
   DO
   BEGIN
     SUSPEND;
   END
END

The BEGIN...END around the SUSPEND; is optional in this case (as it is a single statement), but I prefer to include them always.