pass parameters when create volatile or temp table

[jliang]

Hi,

I try to create a volatile or temp table, but I need pass parameter to the create table statement. I want to know if Teradata allow to do the following statement in the Store Procedure:

Create volatile table test, NO LOG
(col1 varchar(10)
:str1
:str2
,col2 decimal(10,2)
) primary index (col1)
on commit preserve rows;

I try this statement, but can not compile.

Thanks very much for your help in advance.

Jing

 

[CapsuleCorpJX]

Procedures can have "create volatile table" in it, but everything must be hard coded, you can't column names as a variable.

To do what you want, you need to use dynamic sql in your procedure, check here:

http://www.teradata.com/t/go.aspx/index.html?id=117793

Anyway, assuming your statement is correct, here is how I would do it inside the stored procedure. By the way, you forgot a comma between str1 and str2, I've added it in this example. I haven't tested this, so its up to you to see if works:

<begin procedure>
...

set tsql = 'Create volatile table test, NO LOG
(col1 varchar(10)
'||str1||'
,'||str2||'
,col2 decimal(10,2)
) primary index (col1)
on commit preserve rows';

CALL DBC.SysExecSQL(tsql);

<end procedure>

 

[jliang]

It works now. Thanks very much

Jing