1) I have to create a procedure which would perform an insert & then return the inserted row's primary key(identity column value) back to caller.
Code:
CREATE PROCEDURE mySchema.spSavExc (IN EMPDESC VARCHAR(1000) ,IN EMPID VARCHAR(15),IN ORDERID INT)
LANGUAGE SQL DYNAMIC RESULT SETS 1 MODIFIES SQL DATA
P1:BEGIN atomic
declare strCmd VARCHAR(500);
declare strRes VARCHAR(500);
declare X CURSOR with RETURN TO CALLER FOR RES;
SET strCmd='INSERT INTO mySchema.Employee (GENEMPDESC, tstmp,EMPID,OID) VALUES ('''||EMPDESC||''',(select current timestamp from sysibm.sysdummy1),'''||EMPID||''','||ORDERID||')';
SET strRes='SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1';
PREPARE SL FROM strCmd;
EXECUTE SL;
PREPARE RES FROM strRes;
OPEN X;
SET RESULT SETS CURSOR X;
RETURN;
END
;
On running this I get the same id every time with a warning
SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure SPSAVEXC in mySchema. Cause . . . . . : Procedure SPSAVEXC in mySchema was called and has returned one or more result sets. Recovery . . . : None.
SQL State: 01S02
Vendor Code: -99999
Message: Option value changed.
Statement ran successfully, with warnings (16 ms)
1) What have I done wrong here ?
2) Is there a way I can skip the cursor altogether and replace it with an OUT parameter. Please give me an example.
Thankyou.
dBforums
>
Stored procedure returns wrong identity value with warning
11-16-10, 04:57
Linear Mode
