create procedure newuser_add_database
(
eUSER_ID varchar(100),
eEMP_ID varchar(100),
eUSER_NAME varchar(100),
eORGANISATION varchar(100),
eEMAIL_ID varchar(100),
eMOBILE_NO varchar(100),
eCIRCLE_ACCESS varchar(100),
eAPPROVER varchar(100),
eCREATION_DATE date)
Language SQL
modifies sql data

insert into security.user_database_telemedia (USER_ID, EMP_ID, USER_NAME, ORGANISATION, IP_ADDRESS, HOSTNAME, EMAIL_ID, MOBILE_NO, TELEMEDIA, CIRCLE_ACCESS, APPROVER, OLDID, CREATION_DATE)
values( eUSER_ID,eEMP_ID,eUSER_NAME,eORGANISATION,'10.13.2 1.145','CNDABBCUDBDP11',eEMAIL_ID,eMOBILE_NO,
'Y',eCIRCLE_ACCESS,eAPPROVER,eEMP_ID,eCREATION_DAT E);

insert into security.sms_list (MOB_NO,
NAME, CATEGORY, MAIL_ID)
values (eMOBILE_NO,
eUSER_NAME,eCIRCLE_ACCESS,eEMAIL_ID);
commit
end p1
@


When I am trying with only one insert , it is working fine. But when I am trying to give two insert statements it is throwing the error in the second statement,I suppose the scope of the parameter is ending at the terminating semicolon.

Error:
S0022(-206)[IBM][CLI Driver][DB2/AIX64] SQL0206N "EMOBILE_NO" is not valid in the context where it is used. SQLSTATE=42703


The scope of the parameter is becoming local and gets terminated by semi colon.

Try...

create procedure newuser_add_database
(
eUSER_ID varchar(100),
eEMP_ID varchar(100),
eUSER_NAME varchar(100),
eORGANISATION varchar(100),
eEMAIL_ID varchar(100),
eMOBILE_NO varchar(100),
eCIRCLE_ACCESS varchar(100),
eAPPROVER varchar(100),
eCREATION_DATE date)
Language SQL
modifies sql data
p1:
BEGIN
insert into security.user_database_telemedia
...
...;

insert into security.sms_list
...
...;
commit;
end p1
@

I tried this way but i think i am wrong somewhere in compiling it.
Should i use some other command to compile it...
db2 -tvf c:\proc.txt

The 1st error is:
Expected token may include: psm_semicolon. SQLSTATE:42601

2nd error is:
emobile_no is not valid SQLSTATE:42703

You are using the wrong options to create it. Instead of "db2 -tvf ..." you need "db2 -td@ -vf ...". You need to use the correct statement termination character.

Andy

Thanks a lot..its working now....