Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

General
Enthusiast

Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

Hi,

 

I'm trying to find a solution within Teradata to dynamically generate and execute stored procedure calls.

 

I know this can be easily achieved using utilities outside of the RDBMS (e.g. BTEQ) but I would like to keep the processing within the database such as a stored procedure.

 

In BTEQ I would generate the stored proc calls and export to a text file and then execute the file.

 

e.g.

.export file = stored_proc_calls.sql;

SELECT 'CALL MyDB.MyProc();'
FROM MyDB.MyTable
;

.export reset;

.run file = stored_proc_calls.sql;


I've created a stored procedure that can generate the SQL but I can't find a way for the SP to execute the generated SQL.

 

I've tried using DBC.SysExecSQL and cursors but both methods fail. I even attempted for the SP to create a macro to hold the stored proc calls but this fails as macros are multi-statement requests i.e. do not support this functionality.

 

Is anyone aware of a way this can be acheieved or is it technically not possible without something like BTEQ?


Thanks,
Jonny

-- Jonny

Accepted Solutions
Junior Contributor

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

You don't need Dynamic SQL in your case because you always call the same procdure.

 

This should work:

REPLACE PROCEDURE Mask_Core_Tables ()
SQL SECURITY Owner

BEGIN

   DECLARE sql_str VARCHAR(1000);
   DECLARE msg VARCHAR(400);
   
   FOR c AS
      SELECT Trim(DatabaseName) AS DatabaseName,Trim(TableName) AS TableName, ResultSet
      FROM ME_REFERENCE_CORE
      WHERE DATABASE_NAME LIKE '%CORE%'
      GROUP BY 1
   DO
      CALL Mask_Table(c.DatabaseName, c.TableName, msg);
   END FOR;
END;
1 ACCEPTED SOLUTION
8 REPLIES
Senior Supporter

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

From the manual:

SQL Statements that Cannot Be Used Dynamically

...

  • CALL

I guess this will be complicated.

Does the SP contain complex logic? Or is it executing a seq of simple SQL?

 

 

Enthusiast

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

It's meant to be a very simple "wrapper" stored procedure that only generates and executes calls to another stored proc based on data within a table.

 

The below will generate the stored procedure calls into a string variable then execute with DBC.SysExecSQL. The proc compiles successfully but fails on execution with:

 

CALL Failed. 5568:  MASK_CORE_TABLES:SQL statement is not supported within a stored procedure. 

 

REPLACE PROCEDURE Mask_Core_Tables ()

SQL SECURITY OWNER

BEGIN

DECLARE SP_Calls VARCHAR(64000);

SELECT XMLAGG(SP_Calls||'0D'xc ORDER BY 1) (VARCHAR(64000)) INTO SP_Calls
FROM (
	SELECT 
		'CALL Mask_Table ('''|| TRIM(DATABASE_NAME) ||''','''|| TRIM(TABLE_NAME) ||''',ResultSet);' AS SP_Calls
	FROM ME_REFERENCE_CORE
	WHERE DATABASE_NAME LIKE '%CORE%'
	GROUP BY 1
	) dt
ORDER BY 1
;

CALL DBC.SysExecSQL(SP_Calls);

END;

I've tried a few different approaches, such as the proc creating a macro with the generated calls, cursors, but all fail with one error or another.

 

-- Jonny
Senior Supporter

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

Sorry, I was unclear on the SP - I meant the SP you want to call. In the example Mask_Table. What does this SP do? Simply fire SQLs or more advanced conditional logic?

Enthusiast

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

Ah ok - the underlying SP is more complex. It contains 20+ variables, nested procedures, validation, error handling, dynamic and static SQL, UDFs etc

 

But in essence the underlying SP creates/drops tables and moves data between them via generated dynamic SQL.

 

Happy to post the SPL but it contains 430 lines.

-- Jonny
Junior Contributor

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

Your dynamically creates CALL shows three parameters:

CALL Mask_Table (DATABASE_NAME, TABLE_NAME, ResultSet);

What is the 3rd, an IN or OUT parameter?

Enthusiast

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

That's an OUT param

-- Jonny
Tags (1)
Junior Contributor

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

You don't need Dynamic SQL in your case because you always call the same procdure.

 

This should work:

REPLACE PROCEDURE Mask_Core_Tables ()
SQL SECURITY Owner

BEGIN

   DECLARE sql_str VARCHAR(1000);
   DECLARE msg VARCHAR(400);
   
   FOR c AS
      SELECT Trim(DatabaseName) AS DatabaseName,Trim(TableName) AS TableName, ResultSet
      FROM ME_REFERENCE_CORE
      WHERE DATABASE_NAME LIKE '%CORE%'
      GROUP BY 1
   DO
      CALL Mask_Table(c.DatabaseName, c.TableName, msg);
   END FOR;
END;
Enthusiast

Re: Is it possible to dynamically generate and execute stored procedure calls within the RDBMS?

Your reputation precedes you, this worked a treat thank you!

-- Jonny