CODEWITHSUNDEEP
stored-proceduresinsertfirebird
Antonio Rezende
Antonio Rezende

Reputation: 11

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

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!

Upvotes: 1

Views: 6483

Answers (1)

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5481

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.

Upvotes: 1

Related Questions