Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    34

    Unanswered: Return IDENTITY from stored procedure

    I have a table with an IDENTITY column as the PK. I need to create a storedproc that insert a record into this table and returns the IDENTITY VALUE as output parameter. I've found that the function IDENTITY_VAL_LOCAL() gets this
    value, but I can't figure out where to put it in the procedure.

    Can we return identity like this: SET p_ID = IDENTITY_VAL_LOCAL();

    Below is my stored procedure:

    CREATE PROCEDURE Insert_PROJECT
    (
    OUT p_PROJNO int,
    IN p_PROJNAME VARCHAR(24)
    )

    DYNAMIC RESULT SETS 0
    MODIFIES SQL DATA
    LANGUAGE SQL

    BEGIN

    INSERT INTO PROJECT( PROJNAME ) VALUES( p_PROJNAME );
    SET p_PROJNO = IDENTITY_VAL_LOCAL();

    END@

    Thanks in advance for help,

    Kevin

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    SET p_PROJNO = values(IDENTITY_VAL_LOCAL());
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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