Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Angry Unanswered: SQL Stored Procedure Creation Problem

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by lbailey
    SQL0206N "KEYVAR
    " is not valid in the context where it is used.
    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...

  3. #3
    Join Date
    Jul 2004
    Posts
    65
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You might need to refresh the Control Center or even shut it down and restart.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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