Dynamic SQL inside stored procedure that use host varibales

Database
Enthusiast

Dynamic SQL inside stored procedure that use host varibales

Hi! I need to generate a dynamic parameterized SQL statement inside of a stored procedure.
This can be easily done on all the other DBMS by the USING clause at EXECUTE statement, except on Teradata --- or at least I haven't found it.
According the SQL Reference - Stored Procedure manual, one can only use DBC.SysExecSQL to execute a dynamic SQL statement yet this SYsExecSQL does not seem to accept any augment except one string or one string variable. So how do I pass the parameter values that I normally do with USING?

I'm using Teradata V2R6.2.

Thank you.
Regards, Nancy
1 REPLY
Teradata Employee

Re: Dynamic SQL inside stored procedure that use host varibales

You may try constructing the dynamic sql by assiging values to the variables passed or directly using the parameters passed to
the main proc...

something like this ..

REPLACE PROCEDURE testproc ( tablename VARCHAR(30), columnname VARCHAR(30) , columnvalue INTEGER)
BEGIN
DECLARE dyn_sql VARCHAR(10000);

SET dyn_sql = 'update ' || tablename || ' set ' || columnname|| ' = ' || columnvalue || ' ;' ;

CALL dbc.sysexecsql ( : dyn_sql) ;

END;

this is just for illustarion.. you may as well use any available looping logic to do this iteratively etc..

hope this helps