Error in Firebird Procedure

Question

I have to do one query to get one parameter (I need a "1"), but this parameter can be in different cells, for example:

+--------------+--------------+--------------+
|....cell1.....|.....cell2....|....cell3.....|
+--------------+--------------+--------------+
|....here......|.....here.....|....or here...|
+--------------+--------------+--------------+

I have to capture and after, if its equals to one do other query to get what I want. I try to do a procedure with this code:

SET TERM !! ;

CREATE PROCEDURE GET_CASH(
    TICKET VARCHAR(15))
RETURNS ( TOTAL integer
)
AS
BEGIN
    FOR SELECT a.ID_FPAY1, a.FPAY1_IMPORT, a.ID_FPAY2, a.FPAY2_IMPORT, a.ID_FPAY3, a.FPAY3_IMPORT
    FROM TB_TICKETS_HIS a WHERE a.ID_TICKET = TICKET 
    DO
    BEGIN
        IF a.ID_FPAY1 = 1 THEN TOTAL = a.FPAY1_IMPORT;
        IF a.ID_FPAY2 = 1 THEN TOTAL = a.FPAY2_IMPORT;
        IF a.ID_FPAY3 = 1 THEN TOTAL = a.FPAY3_IMPORT;
        SUSPEND;
    END
END!!

SET TERM ; !!

When execute the query apears this error:

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 11, column 5
DO

I try to it works deleting the FOR and Do, appears this error:

SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 11, column 5
BEGIN

Someone can help me with this procedure or explain me what is failing?

Mark Rotteveel · Accepted Answer · 2021-02-27T08:29:17.260

If you want to use a FOR SELECT .. DO (or a singleton select) in Firebird stored procedures, then you need to use INTO to populate parameter values.

See also the Firebird reference on FOR SELECT:

requires an INTO clause that is located at the end of the SELECT ... FROM ... specification. In each iteration of the loop, the field values in the current row are copied to the list of variables specified in the INTO clause. [..]

In other words, depending on what this stored procedure is meant to do, you need to declare additional return variables or local variables to hold the relevant fields of the select, for example:

CREATE PROCEDURE GET_CASH(
    TICKET VARCHAR(15))
RETURNS ( TOTAL integer
)
AS
declare id_fpay1 integer;
declare fpay1_import integer;
declare id_fpay2 integer;
declare fpay2_import integer;
declare id_fpay3 integer;
declare fpay3_import_integer
BEGIN
    FOR SELECT a.ID_FPAY1, a.FPAY1_IMPORT, a.ID_FPAY2, a.FPAY2_IMPORT, a.ID_FPAY3, a.FPAY3_IMPORT
        FROM TB_TICKETS_HIS a WHERE a.ID_TICKET = TICKET 
        into id_fpay1, fpay1_import, id_fpay2, fpay2_import, id_fpay3, fpay3_import
    DO
    BEGIN
        IF a.ID_FPAY1 = 1 THEN TOTAL = a.FPAY1_IMPORT;
        IF a.ID_FPAY2 = 1 THEN TOTAL = a.FPAY2_IMPORT;
        IF a.ID_FPAY3 = 1 THEN TOTAL = a.FPAY3_IMPORT;
        SUSPEND;
    END
END

However you can simplify this a lot by doing that logic within the select statement:

CREATE PROCEDURE GET_CASH(
    TICKET VARCHAR(15))
RETURNS ( TOTAL integer
)
AS
BEGIN
    FOR SELECT case 
            when a.id_pay3 = 1 then a.fpay3_import
            when a.id_pay2 = 1 then a.fpay2_import
            when a.id_pay1 = 1 then a.fpay1_import
        end as total
        FROM TB_TICKETS_HIS a WHERE a.ID_TICKET = TICKET 
        into total
    DO
    BEGIN
        SUSPEND;
    END
END

The reverse order is only to preserve the same logic as you had in your original snippet. Technically, to preserve that logic, it would need extra handling to preserve the previous value of total as the code you wrote would do, but I assume that is a bug in your code.

Error in Firebird Procedure

1 Answers1