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.

How many rows do you expect this procedure to produce: a single row or possibly more than one row? Please provide sample data and expected output for that sample data. — Mark Rotteveel, Nov 24 '19 at 11:07
Yes, I am expecting the result two be two columns with multiple rows — gdekoker, Nov 24 '19 at 11:23
are you coming from MS SQL background? In Firebird making such an SP is usually bad idea. Better make a `VIEW`. Surely, in a View you can not pass parameters, but you might use usual `WHERE` — Arioch 'The, Nov 25 '19 at 09:28
I am not coming from any SQL background, I am learning on the go here. If you don't mind me asking, why is this SP a bad idea? — gdekoker, Nov 25 '19 at 14:43
Because it hides "implementation detail", it is a black box. It can not use indexes, for example, because it does not know where the data, ejected by SP, was taken from. Make & fill two tables both with PK, then do `select * from t1,t2 where t1.id=t2.id` - and check the `query plan`. You would see indexes used to match rows. Now wrap both tables into such SPs and join SPs - now you would have much slower `natural scan` on BOTH. n*m scaling instead of linear. Also read Firebird FAQ about joining of tables with SP, there are some gotchas, reasonable retroactively, but u better read in advance — Arioch 'The, Nov 26 '19 at 07:46

Mark Rotteveel · Accepted Answer · 2021-01-01T11:18:52.563

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.

Resolving a singleton error in a stored procedure

1 Answers1