Loop over sql in Teradata

sql_loop_teradata.sql

-- In Teradata, you can use a Stored Procedure to loop over SQL statements with variable values. 
-- Here is an example of how you might do this:

REPLACE PROCEDURE Test (OUT r2 VARCHAR (3000)) 
BEGIN 
    DECLARE RowCnt INT; 
    DECLARE i INT; 
    DECLARE CurrRow INT; 
    DECLARE r VARCHAR (3000); 

    SET CurrRow = 1; 
    SET r = 'SELECT '; 
    SET RowCnt = (SELECT Count (*) FROM tableWithSQLStmnts ); 

    WHILE CurrRow <= RowCnt DO 
        BEGIN 
            SET r = r ||  'MAX ( CASE Seq WHEN ' || CAST ( CurrRow AS VARCHAR (10) ) || ' THEN '' , '' || SqlStmnt  ELSE '''' END ) '  || CASE WHEN CurrRow = RowCnt THEN '' ELSE ' || '  END;  
            SET CurrRow = CurrRow + 1 ;  
        END; 
    END WHILE;  

    SET r = r || ' FROM ( SELECT SqlStmnt, ROW_NUMBER () OVER ( PARTITION BY TbName ORDER BY SQlStmnt )  FROM tableWithSQLStmnts t ) D ( SqlStmnt ) GROUP BY TbName  ;';  
    SET r2 = r; 

    CALL dbc.sysexecsql (:r); 
END;

-- This procedure loops over the rows in tableWithSQLStmnts, concatenating the SqlStmnt field from each row to form a new SQL statement1. 
-- The WHILE loop is used to iterate over the rows, and the CASE statement is used to handle the concatenation differently for the first and last rows1.

-- Please replace tableWithSQLStmnts with your actual table name and make sure that your table has the columns Seq, TbName, and SqlStmnt.
-- Also, please note that this is just an example and might need adjustments based on your specific use case.

-- Remember to always test your procedures with a small subset of data before running them on your entire dataset.