Stored Procedures - Use dynamic cursor for any table

Database
Enthusiast

Stored Procedures - Use dynamic cursor for any table

I'm attempting to develop a stored procedure that takes a table name as a parameter, and performs column-by-column updates to whatever table is passed in.

I have the dynamic SELECT query working correctly:

SET CURSOR_SQL = 'SELECT * FROM ' || P_TABLE_NAME || ' ORDER BY rsid;';
PREPARE CURSOR_STATEMENT FROM CURSOR_SQL;
OPEN CDC_CURSOR;
/* logic */
CLOSE CDC_CURSOR;

Now I'm at the point where I need to fetch the cursor data into local variables...but I can't create table-specific local variables because I don't know what tables will be passed into this procedure.

Is there a way to iterate over each column in the current cursor row, or are cursors confined to use when you know exactly what columns will be in them?

If my method is impossible, do you have any suggestions for how to approach my problem? I need to use cursors because I am applying change-data-capture data, and must apply the changes in the order they occurred.

Thanks
4 REPLIES
Teradata Employee

Re: Stored Procedures - Use dynamic cursor for any table

Hi,

From what I have understood .... you can use Volatile-table .... insert in this table the list of columns of the P_TABLE_NAME from DBC.COLUMNS .... and iterate through the volatile-table to go through columns of the table.

HTH!

Regards, MAC

Re: Stored Procedures - Use dynamic cursor for any table

hELLO,

I am also developing the SP for CDC which wil be paramatarised, can you please let me know how did you managed to solve this problem?

Enthusiast

Re: Stored Procedures - Use dynamic cursor for any table

Can you post the code. 

I have a table (items) with two columns etc. column A, Column B

A             B

item1     23

item2    24

I have another table (itemline) 

item1     item2

I am looking to update (itemline) based on items table like this:

item1    item2   item3

23         24        100

can you please help.  I am trying to do a crosstab based on my first populated table.

Senior Apprentice

Re: Stored Procedures - Use dynamic cursor for any table

Sorry, i don't get it.

Could you rephrase your question?

Is there any relation to the original question? 

Dieter