All Forums Database
boriscb 22 posts Joined 09/13
28 Mar 2014
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)
    • 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.

Fred 1096 posts Joined 08/04
31 Mar 2014

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.

YouriD 9 posts Joined 01/14
01 Apr 2014

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

boriscb 22 posts Joined 09/13
01 Apr 2014

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:


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




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

.run file d:/myfolder/bteqdemoscript.txt




BTEQ can be set up as a batch script and scheduled, etc.
Could be useful to someone else...... ;o))

You must sign in to leave a comment.