Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Unanswered: problem while building Stored Procedure

    Hi,

    I need to write a stored procedure which should be able to update the columns in a table where column name should be passed as the input parameter in the procedure..

    I have created a procedure with the following code.

    CREATE PROCEDURE SYSPROC.VLSPARUN
    (
    IN IN_COLUMN_NM VARCHAR(20),
    OUT OUT_SCHEMA_NAME varchar(20),
    OUT OUT_SQLCODE INTEGER,
    OUT OUT_SQLSTATE CHAR(5),
    OUT OUT_MESSAGE VARCHAR(3000)
    )


    In the procedure I have written the following query

    UPDATE
    MD1.T5420_COMM_DETAIL SET IN_COLUMN_NM='Arunpp' where prod_id=26286;
    commit;



    I am able to build the stored procedure properly. But when I tried to execute the stored procedure I got the following error.

    "SYSPROC".VLSPARUN - Exception occurred while running:
    A database manager error occurred.[IBM][CLI Driver][DB2] SQL0206N "IN_COLUMN_NM" is not valid in the context where it is used. SQLSTATE=42703
    "SYSPROC".VLSPARUN - Roll back completed successfully.
    "SYSPROC".VLSPARUN - Run failed.


    Please help me in resolving the same.
    Any help would be highly appreciated.

    Thanks
    Yogesh

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to prepare a dynamic SQL statement and then execute it (or open it if it is a cursor). See the DB2 Application Development Guide for more information on dynamically prepared statements.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2007
    Posts
    11

    problem while building Stored Procedure

    Hi Marcus,

    thanks for such a quick reply. We dont have any other application programming language to support this. will it be possible for us to write the dynamic sql statement within the DB2 stored procedure itself.

    Thanks
    Yogesh

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, look at the "PREPARE" and "EXECUTE" statements. You can also open a cursor that has been built with a PREPARE.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2007
    Posts
    11

    problem while building Stored Procedure

    Hi Marcus,

    I tried writing the dynamic query. I was able to build the stored procedure with the dynamic select query, but still I am not able to write a dynamic update query. It will be a great help if you can provide me with some sample code for writing a dynamic update statement in DB2 stored procedure

    Thanks
    Yogesh

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Yogesh Pandit
    It will be a great help if you can provide me with some sample code for writing a dynamic update statement in DB2 stored procedure
    I am sure that it would be a great help to you. It would be a great help to me if you would provide me $100,000.

    Another idea is that you look at the Application Development Guide: Programming Server Applications manual and figure it out.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Dynamic SQL is a really frequently asked question. You may want to search here in this forum or use your favorite search engine.

    p.s: IMHO, this is a basic concept in relational database systems and should be taught in every good database course. How come that this question pops up so often, I wonder?
    Last edited by stolze; 01-31-08 at 04:29.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Stolze

    I agree 100%. Dynamic SQL is not taught which is a problem. You can tell by the same tired old homework questions posted here and elsewhere. I've worked in shops where the DBA's prohibit dynamic SQL!!! They don't like the idea of SQL in their production environment that could, OMG, change!!! I've worked at clients that don't want dynamic SQL because they don't understand it and therefore fear it. I think education and patience are the keys.


    Yogesh

    People are going to be much more willing to help after you try to help yourself. If you come back with a specific question saying I'm having a problem with x, and after trying y and z and looking through the manuals, I can't resolve the problem. People here enjoy sharing their knowledge, but don't want to do your job.

  9. #9
    Join Date
    May 2003
    Posts
    113
    the -206 complains about this stmt:
    UPDATE
    MD1.T5420_COMM_DETAIL SET IN_COLUMN_NM='Arunpp' where prod_id=26286;

    the sql stmt is trying to update TABLE: "MD1.T5420_COMM_DETAIL", by setting the table's column "IN_COLUMN_NM". This "IN_COLUMN_NM" is not the argument of your stored proc.

    the sqlcode complains that it can't find the column named "IN_COLUMN_NM"

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In the manual I posted above, there is an example of a dynamic insert statement in a procedure. Even a novice should be able to read the manual and apply the insert example to creating an update statement.
    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
  •