I have a Procedure that performs the "inserts" and "updates" in the tables. The need to create it was to try to centralize all the scan functions before inserting or updating records. Today there was a need to return the value of the table's "ID" field so that my application can locate the registry and perform other procedures.
Stored Procedure:
SET TERM ^ ;
CREATE OR ALTER procedure sp_insupd (
iaction varchar(3),
iusuario varchar(20),
iip varchar(15),
imodulo varchar(30),
ifieldsvalues varchar(2000),
iwhere varchar(1000),
idesclogs varchar(200))
returns (
oid integer)
as
declare variable vdesc varchar(10000);
begin
if (iaction = 'ins') then
begin
vdesc = idesclogs;
/*** o erro está na linha abaixo ***/
execute statement 'insert into '||:imodulo||' '||:ifieldsvalues||' returning ID into '||:oid||';';
end else
if (iaction = 'upd') then
begin
execute statement 'select '||:idesclogs||' from '||:imodulo||' where '||:iwhere into :vdesc;
execute statement 'execute procedure SP_CREATE_AUDIT('''||:imodulo||''');';
execute statement 'update '||:imodulo||' set '||:ifieldsvalues||' where '||:iwhere||';';
end
insert into LOGS(USUARIO, IP, MODULO, TIPO, DESCRICAO) values (
:iusuario, :iip, :imodulo, (case :iaction when 'ins' then 1 when 'upd' then 2 end), :vdesc);
end^
SET TERM ; ^
The error in the above line is occurring due to syntax error. The Procedure is compiled normally, ie the error does not occur in the compilation, because the line in question is executed through the "execute statement". When there was no need to return the value of the "ID" field, Procedure would normally work with the line like this:
...
execute statement 'insert into '||:imodulo||' '||:ifieldsvalues||';';
...
What would be the correct way for the value of the "ID" field to be stored in the "OID" variable?