All Forums Database
MattW At ATT 11 posts Joined 06/06
27 Jun 2006
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;BeginEXECUTE ( 'Update temp set temp.job = ''''the date is '''' ' || temp.startdate);End Select *From Temp

dnoeth 4628 posts Joined 11/04
28 Jun 2006

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


Fred 1096 posts Joined 08/04
28 Jun 2006

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

MattW At ATT 11 posts Joined 06/06
29 Jun 2006

Ok that was a poor example. I am actually trying to do something more likeExecute ('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?

You must sign in to leave a comment.