CALLing a dynamically created SP name.

Database
Enthusiast

CALLing a dynamically created SP name.

Hi ,

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:

  1. Look up the tablenames in a specific sequence from a control table.
  2. Makeup the SP name from the tablename e.g.: SET SPNAME = 'sp_load'||tablename;
  3. CALL SPNAME .... I have tried ...
    • CALL(:SPNAME); or (SPNAME)
    • EXECUTE IMMEDIATE (:SPNAME); or (SPNAME)
    • CALL DBC.SysExecSQL (:SPNAME); or (SPNAME)

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.

Boris.

3 REPLIES
Teradata Employee

Re: CALLing a dynamically created SP name.

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.

Enthusiast

Re: CALLing a dynamically created SP name.

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);

Enthusiast

Re: CALLing a dynamically created SP name.

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:

BTEQ

.logon TestDB/User,xxxxxxx;

 

--Step 1. Read the TablesToTransform table and create call statements for BTEQ

.EXPORT FILE = d:/myfolder/bteqdemoscript.txt

--  Note: The (TITLE '') is used to suppress the normal column name and '---' in BTEQ

SELECT 'CALL TestDB.sp_'||TableName||'();' (TITLE '') FROM TestDB.TablesToTransform ;

.EXPORT RESET;

 

 

--Step 2. Run the set of SP's in the Export File

.run file d:/myfolder/bteqdemoscript.txt

.logoff;

.quit;

 

BTEQ can be set up as a batch script and scheduled, etc.

Could be useful to someone else...... ;o))