0

How to pass the table name as a parameter/variable in teradata stored procedure to a select statement inside a loop? How to pass this table name <td_table_name> dynamically. I have a list of tables in another table and want to pass them in a loop.

REPLACE PROCEDURE TD_STORED_PRC()
BEGIN
  DECLARE v_match_count INT;
  FOR i AS cur CURSOR FOR 
    SELECT col_name
    FROM **<td_table_name> **   ---- "this table name to read from input_table_list table and assign that table name value here"
  DO
    BEGIN
      SET v_match_count = 0;
      
      FOR j AS inner_cur CURSOR FOR 
        SELECT col_name
        FROM td_table_name2
        WHERE col_name = i.col_name
      DO
      SET v_match_count = v_match_count + 1;
        BEGIN
          INSERT INTO table3 (STATUS, INSERT_DATE_TIME) 
          VALUES ( 'Found', CURRENT_TIMESTAMP);
        END;
      END FOR;
    END;
  END FOR;
END;

I tried to write a subquery to get the table names from input_table_list but I think we can't pass table name using subquery. Is there a way to pass the table names from input_table_list in loop to the above procedure?

user3737395
  • 1
  • This sort of nested looping likely does not perform well. Look for ways to leverage SQL and avoid procedural row-at-a-time logic. That said, to supply a table name as a parameter you will need to use dynamic SQL - EXECUTE IMMEDIATE if you only need to check status or DECLARE CURSOR FOR *statement* / PREPARE *statement* FROM *string* if you need to fetch data. See https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Stored-Procedures-and-Embedded-SQL/SQL-Stored-Procedures/Using-Dynamic-SQL-in-Stored-Procedures – Fred Jun 05 '23 at 14:50
  • I don't fully understand what you want, but [this](https://stackoverflow.com/a/28638552/2527905) is an example using a cursor and Dynamic SQL – dnoeth Jun 05 '23 at 14:53

0 Answers0