Assistance with Firebird SQL stored procedure

Question

I have a very simple stored procedure:

ALTER PROCEDURE SP_BALANCE_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
END;

I am getting an error:

TOKEN UNKNOWN LINE 22 COLUMN 1.

Line 22 column 1 refers to the last line of the code.

Somewhere I am making a really simple syntax error and for some reason I am not able to track it down. Any assistance will be greatly appreciated.

***WHAT*** error are you getting?? Remember: we **cannot** see and read your screen, nor can we read your mind - you'll have to **SHOW US** the exact and complete error message you're getting ! — marc_s, Nov 24 '19 at 09:05
Hi, apologies. I thought my mistake is very straightforward that someone will be able to point it out straight away. The error message is: TOKEN UNKNOWN LINE 22 COLUMN 1. Line 22 column 1 refers to the last line of the code — gdekoker, Nov 24 '19 at 09:18

score 2 · Answer 1 · answered Nov 24 '19 at 11:05

A token unknown error always includes the unknown token in the error message, in this case the unknown token is END:

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 23, column 1
END

Inside a stored procedure, each statement must end with a semi-colon.

The corrected procedure is:

ALTER PROCEDURE SP_BALANCE_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;
END

Notice the ; after AMOUNT on the last line of the SELECT statement.

Assistance with Firebird SQL stored procedure

1 Answers1