On 10-6-2018 01:14, talorigomat@... [firebird-support] wrote:

> When I try to create the following stored procedure I get sql error code
> = -206 Column unknown EMPID
>
>
> SET TERM ! ;
> CREATE OR ALTER PROCEDURE TEST (
>   ATITLE DOM_VARCHARTINY,
>   AFIRST_NAME DOM_VARCHARMEDIUM,
>   AINITIALS DOM_VARCHARTINY,
>   ASURNAME DOM_VARCHARMEDIUM,
>   AEMAIL DOM_VARCHARLARGE,
>   APHONE DOM_VARCHARTINY)
> AS
> begin
>       INSERT INTO Employee (title, first_name, initials, surname,
>                              email, phone_ext)
>       VALUES(:ATitle, :AFirst_Name, :AInitials, :ASurname, :AEmail,
> :APhone)
>       RETURNING :EMPID;
> end !
> SET TERM ; !
>
> The table does have a EMPID c olumn defined and if I type the insert
> statement and execute it, it does work.

Your code has a number of problems:

1. The :EMPID is a stored procedure variable reference, not a column
reference, and you don't have a variable :EMPID defined.
2. When using INSERT .. RETURNING .. in a stored procedure, you must use
INSERT .. RETURNING .. INTO ..
3. When returning values from a stored procedure, you must explicitly
declare the return values (assuming you want to return it from the
stored procedure).

In other words, change your code to:

SET TERM ! ;
CREATE OR ALTER PROCEDURE TEST (
ATITLE DOM_VARCHARTINY,
AFIRST_NAME DOM_VARCHARMEDIUM,
AINITIALS DOM_VARCHARTINY,
ASURNAME DOM_VARCHARMEDIUM,
AEMAIL DOM_VARCHARLARGE,
APHONE DOM_VARCHARTINY)
returns (aempid bigint) -- or whatever data type empid is
AS
begin
INSERT INTO Employee (title, first_name, initials, surname,
email, phone_ext)
VALUES(:ATitle, :AFirst_Name, :AInitials, :ASurname, :AEmail,
:APhone)
RETURNING empid into :aempid;
end !
SET TERM ; !

BTW: Why define this stored procedure? You can just as well use the
insert directly.

If you just want to use the returned id with the stored procedure, do
something like:

SET TERM ! ;
CREATE OR ALTER PROCEDURE TEST (
ATITLE DOM_VARCHARTINY,
AFIRST_NAME DOM_VARCHARMEDIUM,
AINITIALS DOM_VARCHARTINY,
ASURNAME DOM_VARCHARMEDIUM,
AEMAIL DOM_VARCHARLARGE,
APHONE DOM_VARCHARTINY)
AS
declare aempid bigint; -- or whatever data type empid is
begin
INSERT INTO Employee (title, first_name, initials, surname,
email, phone_ext)
VALUES(:ATitle, :AFirst_Name, :AInitials, :ASurname, :AEmail,
:APhone)
RETURNING empid into :aempid;
end !
SET TERM ; !

--
Mark Rotteveel