Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    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.

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •