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

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.

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

Yes, look at the "PREPARE" and "EXECUTE" statements. You can also open a cursor that has been built with a PREPARE.

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

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.

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?

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.

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"

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.