Hello. I am new to this forum so if I have put this question in the wrong place, forgive me and tell me where I can get better help. Now for the question.

I am trying to create a stored procedure to insert data for a table (DB2). Here is the code:

CREATE PROCEDURE SAT.CREATE_SOLUTION
(IN EINOTEID INTEGER,
IN NAME VARCHAR(200),
IN SECTOR VARCHAR(20),
IN INDUSTRY VARCHAR(30),
IN TYPE VARCHAR(25),
IN START DATE,
IN END DATE,
IN COMPLETE DATE)

MODIFIES SQL DATA
LANGUAGE SQL

P1: BEGIN


DECLARE KeyVar INTEGER;
DECLARE s_solname VARCHAR(200);
DECLARE s_sector VARCHAR(20);
DECLARE s_industry VARCHAR(30);
DECLARE s_stype VARCHAR(25);
DECLARE s_sdate DATE;
DECLARE s_edate DATE;
DECLARE s_cdate DATE;

SET KeyVar = SAT.SOLUTION.EINOTEID;
SET s_solname = SAT.SOLUTION.NAME;
SET s_sector = SAT.SOLUTION.SECTOR;
SET s_industry = SAT.SOLUTION.INDUSTRY;
SET s_stype = SAT.SOLUTION.TYPE;
SET s_sdate = SAT.SOLUTION.START;
SET s_edate = SAT.SOLUTION.END;
SET s_cdate = SAT.SOLUTION.COMPLETE;


INSERT INTO SAT.SOLUTION (EINOTEID, NAME, SECTOR, INDUSTRY, TYPE, START, END, COMPLETE)
VALUES (KeyVar, s_solname, s_sector, s_industry, s_stype, s_sdate, s_edate, s_cdate);


END P1

These columns (EINOTEID, NAME, SECTOR, INDUSTRY, TYPE, START, END, COMPLETE) are the ones located in the actual table. These columns (KeyVar, s_solname, s_sector, s_industry, s_stype, s_sdate, s_edate, s_cdate) are the fields I need to be entered. When I run this code I keep getting this error.
SQL0206N "KEYVAR
" is not valid in the context where it is used.

For some reason the compiler is not picking up the declared variables. I looked through the forum for some samples and I tried another version without using local variables. Code below:

CREATE PROCEDURE SAT.CREATE_SOLUTION
(IN KeyVar INTEGER,
IN s_solname VARCHAR(200),
IN s_sector VARCHAR(20),
IN s_industry VARCHAR(30),
IN s_stype VARCHAR(25),
IN s_sdate DATE,
IN s_edate DATE,
IN s_cdate DATE)

MODIFIES SQL DATA
LANGUAGE SQL

P1: BEGIN

INSERT INTO SAT.SOLUTION (EINOTEID, NAME, SECTOR, INDUSTRY, TYPE, START, END, COMPLETE)
VALUES (KeyVar, s_solname, s_sector, s_industry, s_stype, s_sdate, s_edate, s_cdate);


END P1

This version runs without errors; however, the procedure is not showing up in the database(nor is it saying command completed sucessfully). I tried to drop it to see if it is actually on the server, but it wasn't.

Do you have any suggestions on how to fix either one of these codes? This procedure has been giving me hassels and it is a big part of the database I am trying to create so thanks for any help ahead of time.

LeRoy

Is this the actual error message? I mean, does the word "KEYVAR" in fact appear on the separate line from the "is not valid...." text? If so, you may have some formatting problem with your source file....It looks like an extra LF made its way there...

Hi Leroy:
I have a similar problem. I compiled a SP and it doesn't show in Database objects (Control Center), even though it works...
Does anybody know what happens?,

Thanks a lot.

that is right

in 1st procedure code

you are getting following parm for procedure input parms


(IN EINOTEID INTEGER,
IN NAME VARCHAR(200),
IN SECTOR VARCHAR(20),
IN INDUSTRY VARCHAR(30),
IN TYPE VARCHAR(25),
IN START DATE,
IN END DATE,
IN COMPLETE DATE)



but you set statements , you setting to diffrent , those not declare , unknow to procedure


in 2nd procedure your are assing procedure input parm to your declare parms

that is why 2nd one is working.


one questions , if you are inserting date from one table to another

each insert you calling this procedure or , insert all data from source table, if it is you declare cursor for source table , insert into target table.


if any questions , let me know

my email id : lekharaju.ennam@famu.edu


Thank You

Lekharaju Ennam

You might need to refresh the Control Center or even shut it down and restart.