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

SET p_PROJNO = values(IDENTITY_VAL_LOCAL());