Hi Folks,

I am in the process of updating a table in DB2. For that purpose I have written a stored procedure copied below.

CREATE PROCEDURE ECSYS.RECO_UPDATE_USER (IN NEW_ID VARCHAR(250), IN OLD_ID VARCHAR(250))
LANGUAGE SQL
P1: BEGIN
DECLARE NEW_ID_ENCRYPTED CHAR(16);
DECLARE RECO_NEW_ID VARCHAR(250);
DECLARE RECO_OLD_ID VARCHAR(250);

SET RECO_NEW_ID = NEW_ID ;
SET RECO_OLD_ID = OLD_ID ;

DECLARE C1 CURSOR WITH RETURN FOR
SELECT EC_ID FROM ECSYS.ECID_UID_MAP WHERE ECID_UID_MAP.LDAP_UID = RECO_NEW_ID;

OPEN C1;
FETCH C1 INTO NEW_ID_ENCRYPTED;
UPDATE ECSYS.TASKS SET CREATED_BY_ID = NEW_ID_ENCRYPTED
WHERE TASKS.CREATED_BY_ID = ECID_UID_MAP.EC_ID AND ECID_UID_MAP.LDAP_UID = RECO_OLD_ID;

CLOSE C1;
END P1

I stored the above procedure in "update_user.db2" file and issuing the following command to execute it from the CLP.
db2 call update_user(:NEW_ID :xxxx, :OLD_ID :yyy)

But I am getting the following error:
SQL0312N The host variable "NEW_ID" is used in a dynamic SQL statement, a
view definition, or a trigger definition. SQLSTATE=42618

Please let me know where I am doing wrong. Any information on this subject would be greatly appreciated

Thanks and best regards,
Krishna Prasad.