I am getting a "invalid request BLR at offset 667 bad parameter number" when I
try to define the stored procedure below. The procedure first checks that the
value supplied in IN_ORGID relates to a department and then finds the
organisational structure for the department (Ie: it's division and subdivision)
and insert these into a table called assignment. I've placed a comment where I
believe the error is occurring.
SET TERM ^ ;
Create PROCEDURE INSERTASS
(
IN_GDLID DOM_INTLARGE,
IN_ORGID DOM_INTLARGE,
IN_ASSDTE DOM_Date,
IN_EMPID DOM_INTLARGE,
IN_STEID DOM_INTSMALL
)
AS
Declare LevelCheck integer;
Declare POrgExist smallint;
Declare Vorgid BigInt;
BEGIN
select count(orgid) from organisation where orgid = :In_Orgid and level_num
=3
into LevelCheck;
if (LevelCheck =1) then
begin
Vorgid =0;
/* get the organisational structure for in_orgid (Division,
Subdivision,)*/
for select p.parentid
from orgchart p, orgchart c
inner join organisation pn on pn.orgid = p.parentid
inner join organisation cn on cn.orgid = c.childid
where p.childid=10 and c.parentid= 10
and p.depth+c.depth+1 between 2 and 3 into Vorgid
do
begin
/* Check whether the Level 1 or 2 (Division or Subdivision) info already
exist
in the assignment table for the guidance and site combination. For
Division and Subdivision
Site is always null. Specialty can be null or include a value*/
Select count(assid)from Assignment where gdlid=:in_gdlid
and orgid = :IN_ORGID into PorgExist;
if (PorgExist =0) then
/* <--- Error appears to occur with the insert statement below --->*/
INSERT INTO Assignment (assid, GdlId, OrgId, Assigned_dte, empid, steid)
VALUES (gen_id(AssKey_Gen, 1), :IN_GDLID, :Vorgid, :IN_ASSDTE, NULL, NULL);
end
end
else
exception nowork;
END
^
SET TERM ; ^
COMMIT WORK;
