Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Unanswered: create procedure with IN .. IN

    Can i use two "IN's" in a single procedure. If so please tell me whether below code is with right syntax. While executing it im getting some errors.


    CREATE PROCEDURE test
    (IN EMPLOYEE_NUMBER CHAR(10),
    IN RATE DECIMAL(6,2))
    LANGUAGE SQL
    SELECT * FROM EMP ;

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    sp

    yes multi in is supported
    why don't you indicate the error and when does it occur ?
    see create procedure in info center for detailed info
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Oct 2006
    Posts
    83
    thank u ,
    i am unable to trace out this.i compared this syntax with db2 create procedure syntax in info centre.
    error message 0104N an unexpected token "from" found after language

    kindly resolve this

  4. #4
    Join Date
    Jan 2003
    Posts
    35
    You cannot directly write a select stmt in a stored proc. You will have to write a cursor for fetching the output of the select in some variables. For eg suppose the emp table has two smallint columns the syntax of the stored procedure can be

    CREATE PROCEDURE test
    (IN EMPLOYEE_NUMBER CHAR(10),
    IN RATE DECIMAL(6,2))
    LANGUAGE SQL
    begin
    Declare a smallint;
    Declare b smallint;
    declare c1 cursor for
    SELECT * FROM EMP ;

    fetch c1 into a,b;
    end

  5. #5
    Join Date
    Nov 2006
    Posts
    4
    the cursor need open before fetch or close

  6. #6
    Join Date
    Jan 2003
    Posts
    35
    Thanks .Below is the corrected stmt

    CREATE PROCEDURE test
    (IN EMPLOYEE_NUMBER CHAR(10),
    IN RATE DECIMAL(6,2))
    LANGUAGE SQL
    begin
    Declare a smallint;
    Declare b smallint;
    declare c1 cursor for
    SELECT * FROM EMP ;
    open c1;
    fetch c1 into a,b;
    close c1;
    end

Posting Permissions

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