I have a situation where I need to execute a series of "manufactured" SP names in a loop.
This is the scenario:
From my LANDING database I need to ETL a series of tables to the WAREHOUSE database. Each table has its own transformation requirements.
This is what I am trying to achieve:
Nothing seems to work for me.
Is there a specific way in Teradata to CALL a dynamic SQL statement that consists of an SP name ?...
Any suggestion please !....
Thank you for your time.
CALL is not allowed as a dynamic SQL statement within a Stored Procedure.
You would need to construct the 'CALL sp_loadtablename' statement on a client platform (e.g. an ETL server) and send that to the database.
Don't know if it will be a success but this is what I would try:
set SPNAME = 'Call sp_load'|| tablename ||';'
CALL dbc.sysexecsql (:SPNAME);
Thank you for your suggestions. I will need to figure out a different approach.
The suggestion from YouriD did not work unfortunately.
The following was suggested as another way in BTEQ:
Could be useful to someone else...... ;o))