Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Unanswered: Stored procedure : column name from a variable

    Hi All,

    I am writing a stored procedure which has two cursors. First cursor returns a string value. This value is a column name of a table which I am using in second cursor. In second cursor, I want to use the value returned by the first cursor as a name of column and not as a string value.

    For example, cursor1 returns a string "USER_ID". Then in cursor2, I want "select * from table where USER_ID = 2 " where USER_ID comes from cursor1. I stored return value of cursor1 in variable TEMP_VALUE and I wrote cursor2 as "select * from table where TEMP_VALUE = 2". I am getting type mismatch error as type of TEMP_VALUE is VARCHAR(30). How to make TEMP_VALUE to be treated as column name rather than a value?

    Thanks,
    rpkulkarni

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Google for "dynamic SQL". Also check out the PREPARE statement in the manual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by rpkulkarni View Post
    I am writing a stored procedure which has two cursors.




    How to make TEMP_VALUE to be treated as column name rather than a value?

    Thanks,
    rpkulkarni
    At the beginning

    set statement1='insert into OUTPUT select * from table where cast(? as char(30)) = 2';
    PREPARE S1 FROM statement1;
    And inside the loop

    EXECUTE S1 using TEMP_VALUE;
    Not tried, but hope this helps.

    DBFinder

  4. #4
    Join Date
    Jul 2009
    Posts
    150

    Thumbs down

    Quote Originally Posted by DBFinder View Post
    At the beginning



    And inside the loop



    Not tried, but hope this helps.

    DBFinder
    Forget about.... Has to be something like this:

    Code:
    set statement1= 
    varchar('insert into OUTPUT select * from table where ' ||  strip(TEMP_VALUE) || ' =  ?'); 
    PREPARE S1 FROM statement1;
    EXECUTE S1 using :host_var;
    You can't use parameter marker as column name.

    Kara
    Last edited by DB2Plus; 11-25-09 at 23:51.

  5. #5
    Join Date
    Nov 2009
    Posts
    11

    How to write Cursor using these solutions

    Hello All,

    I need the column name while declaring the cursor and not for inserting into the table. Thus I can not do PREPARE and then EXECUTE statement. My requirement is

    Code:
    DECLARE c1 cursor for
    select coulmn_name from column_list;
    DECLARE C2 CURSOR WITH RETURN FOR 
    SELECT * FROM TABLE WHERE ? = 1;
    and this ? I need to fill up from return value of cursor1.
    Any advises?

  6. #6
    Join Date
    Jul 2009
    Posts
    150

    Thumbs down Learn manuals

    Quote Originally Posted by rpkulkarni View Post
    Hello All,

    I need the column name while declaring the cursor and not for inserting into the table. Thus I can not do PREPARE and then EXECUTE statement. My requirement is

    Code:
    DECLARE c1 cursor for
    select coulmn_name from column_list;
    DECLARE C2 CURSOR WITH RETURN FOR 
    SELECT * FROM TABLE WHERE ? = 1;
    and this ? I need to fill up from return value of cursor1.
    Any advises?
    You need the complete solution for free ? Tell this to your boss.

    I told you already, you can't use parameter marker as column name.
    This is a rule.

    So, you have to prepare cursor from the statement:

    Code:
    set statement1= 
    varchar('select * from table where ' ||  strip(TEMP_VALUE) || ' =  ?');
    After this go to the manual and learn something.
    5.48 "DB2 V9.1 for z/OS SQL Reference" IBM Library Server

    Kara
    Last edited by DB2Plus; 11-26-09 at 01:11.

  7. #7
    Join Date
    Nov 2009
    Posts
    11

    Thanks for the valuable link

    Hello DB2Plus,

    Thanks for the valuable link. I got the thing running with cursor created over result set where this result set is generated using the prepared statement.

    Regards
    rpkulkarni

Posting Permissions

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