11-16-10, 05:57 #1Registered User
- Join Date
- Mar 2009
Unanswered: Stored procedure returns wrong identity value with warning
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.
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 ;
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.
11-16-10, 09:25 #2Registered User
- Join Date
- May 2003
I am not sure what you are doing, but you may want to look at FINAL TABLE syntax. This allows you to select from an insert statement to find the generated Identity column that was inserted into the table. The select and insert and submitted together as one statement. For example,
Assume that EMPID is the PK of the table and is a generated identity column.
SELECT EMPID from FINAL TABLE (insert into EMPLOYEEE (EMPID, LASTNAME, FRISTNAME) values (default, 'Smith', 'John');M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390