Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Location
    VA,USA
    Posts
    8

    Unanswered: FETCH cursor in STORED PROCEDURE

    Hi,
    I am trying reteive some value from a dynamic SQL used in Stored procedure. The code is as below;

    SET V_SQL_STMT ='SELECT '|| V_ELEMENT_NAME ||
    ' FROM ODS.'||V_TABLE_NAME||' WHERE '|| V_JOIN_COLUMN1||' = ? ';

    PREPARE s1 FROM V_SQL_STMT;
    DECLARE C1 CURSOR FOR s1;
    OPEN c1 using V_KEY1;
    FETCH C1 into V_VALUE;

    But I don't know why its giving error executing these line.
    [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "<cursor declaration>" was found

    I am using DB2 UDB 7.2.6 on AIX. I want to get the output of the dynamic SQL into a variable.
    It would be of great help if any of you can get me through this.
    Thanks and regards,
    Arvind

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Arvind,

    Look int the SQL Reference for the syntax of creating
    Stored Procedures especially the Coumpound Statement.

    There are sections that must be adhered to.
    The DECLARE of the cursor must come before the SQL.
    YOur code should look something like:

    DECLARE C1 CURSOR FOR s1;

    SET V_SQL_STMT ='SELECT '|| V_ELEMENT_NAME ||
    ' FROM ODS.'||V_TABLE_NAME||' WHERE '|| V_JOIN_COLUMN1||' = ? ';

    PREPARE s1 FROM V_SQL_STMT;
    OPEN c1 using V_KEY1;
    FETCH C1 into V_VALUE;

    Andy

  3. #3
    Join Date
    May 2002
    Location
    VA,USA
    Posts
    8
    Thanks a lot all of you..
    It worked...
    regards,
    Arvind

Posting Permissions

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