Is there an Equivilant Function to Execute in Teradata.

Database
Enthusiast

Is there an Equivilant Function to Execute in Teradata.

This is How I would write this in MS SQL SERVER 2003, how would I do this in Teradata. I mean use the Execute function.

-- drop table temp;
Create volatile Table TEMP As
(
Select
'1060101' (int) startdate,
'1060131' (int) enddate,
'monkeytest' (char(30)) job
)
with data PRIMARY INDEX (Startdate, Enddate, job)
ON COMMIT PRESERVE ROWS;

Begin
EXECUTE ( 'Update temp set temp.job = ''''the date is '''' ' || temp.startdate);
End

Select *
From Temp
3 REPLIES
Junior Contributor

Re: Is there an Equivilant Function to Execute in Teradata.

In your example it's not necessary to use EXECUTE :-)

In general, if you want to execute dynamic sql it's only allowed within a SP using a call to dbc.sysexecsql.

Of course it's possible to create a SP similar to:

replace procedure dynamic_sql(in x varchar(64000))
begin
call dbc.sysexecsql(x);
end;

and then it can be used outside of a SP, too.
But then you'll have to think about "sql injection"...

Dieter
Teradata Employee

Re: Is there an Equivilant Function to Execute in Teradata.

CALL DBC.SysExecSQL('Update temp set temp.job = ''the date is '' ' || temp.startdate)

Note that to use this feature, the "owner" of the SP (the database/user in which the procedure is defined) must be the same as the "creator" (user issuing the CREATE PROCEDURE).
Enthusiast

Re: Is there an Equivilant Function to Execute in Teradata.

Ok that was a poor example. I am actually trying to do something more like

Execute ('create table '|| temp.tablename|| ' yadda yadda' where I'd create a different table per month and increment the table name number based on the month.

Would the CALL DBC.SysExecSQL work in that scenario?