Firebird "insert ... returning" into a variable using stored procedure

Question

I'm using Firebird 2.x and I have made a stored procedure to insert a record if it doesn't exist and return its ID into a variable.

But when I execute, it turns out that the following error occurs:

Dynamic SQL Error. SQL error code = -104. Unexpected end of command - line 2, column 76.

Full source code of my SP following:

CREATE PROCEDURE INSERT_ADMIN_OFFICE
AS
DECLARE VARIABLE OFF_ID BIGINT;
DECLARE VARIABLE PER_ID BIGINT;
DECLARE VARIABLE EMP_ID BIGINT;
DECLARE VARIABLE AP_ID BIGINT;
BEGIN
     IF (NOT EXISTS(SELECT * FROM OFFICE OFF WHERE OFF.DESCRIPTION LIKE '%Administrador%')) THEN
          INSERT INTO OFFICE (DESCRIPTION) VALUES ('Administrador') RETURNING ID INTO :OFF_ID;
     ELSE
          SELECT OFF.ID FROM OFFICE OFF WHERE OFF.DESCRIPTION LIKE '%Administrador%' INTO :OFF_ID;

     INSERT INTO PERSON (NAME, BIRTH_DATE, ADDRESS, DISTRICT, CITY, STATE) VALUES ('Intellitools Desenvolvimento de Software Ltda.', '01/01/2007', 'Rua Nunes Machado, 472 - Cj 503', 'Centro', 'Curitiba', 'PR') RETURNING ID INTO :PER_ID;
     INSERT INTO USER_PASSPORT (PERSON_ID, USER_NAME, PWD, TYPE) VALUES (:PER_ID, 'intellitools', 123, 1);
     INSERT INTO EMPLOYEE (OFFICE_ID, PERSON_ID) VALUES (:OFF_ID, :PER_ID) RETURNING ID INTO :EMP_ID;
     INSERT INTO ACCESS_PROFILE (DESCRIPTION) VALUES ('Administrador Geral') RETURNING ID INTO :AP_ID;
     INSERT INTO REL_EMPLOYEE_ACCESS_PROFILE (EMPLOYEE_ID, ACCESS_PROFILE_ID) VALUES (:EMP_ID, :AP_ID);
SUSPEND;
END
;

I notice that this error is because of the INTO on the INSERT but I can't find another way to do that.

I appreciate your help!

please, provide full source code of your SP and metadata for OFFICE table. — Andrej Kirejeŭ
– Andrej Kirejeŭ, Commented Dec 23, 2011 at 16:07
Why do you use SUSPEND when your procedure doesn't return any columns? — Andrej Kirejeŭ
– Andrej Kirejeŭ, Commented Dec 23, 2011 at 16:26
SELECT rdb$field_name FROM rdb$relation_fields WHERE rdb$relation_name = 'OFFICE' this query will get you a list of all fields of OFFICE table. — Andrej Kirejeŭ
– Andrej Kirejeŭ, Commented Dec 23, 2011 at 16:27
Just remove SUSPEND and your proc will execute like a charm. For a one time actions I would suggest EXECUTE BLOCK instead of creating stored procedure. — Andrej Kirejeŭ
– Andrej Kirejeŭ, Commented Dec 23, 2011 at 18:49

Andrej Kirejeŭ · Accepted Answer · 2011-12-23 18:50:34Z

1

Just remove SUSPEND and your proc will execute like a charm. For a one time actions I would suggest EXECUTE BLOCK instead of creating stored procedure.

answered Dec 23, 2011 at 18:50

Andrej Kirejeŭ

5,5612 gold badges29 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Firebird "insert ... returning" into a variable using stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related