Dynamic Table name in from clause in SP

[GeneCampbell]

I am trying to parse a table name from a field in a teradata table, pass the table name to a variable and use the variable to populate the table name in a select statement.

Is this possible?



create procedure core_proddb_s.table_nme2( in p1 int )
begin
Declare var1 char(50);
select table_name into:var1 from core_workdb.load_parameter where campaign_id=1;
insert into core_workdb.opportunity_test
select * from :var1;

End;

 

[tdatgod]

Hi,
I see from you other post you have found the dynamic SQL syntax.

create procedure core_proddb_s.table_nme3( in p1 char(50), out p2 char(50) )
begin
call DBC.SysExecSql('Select campaign_id from ' || 1);

End;


Therefore you might want to try it on this query.



create procedure core_proddb_s.table_nme2( in p1 int )
begin
Declare var1 char(50);

select table_name into:var1 from core_workdb.load_parameter where campaign_id=1;

call DBC.SysExecSql('insert into core_workdb.opportunity_test ' ||
'select * from ' || :var1 );

End;



As I said in the other post SELECT, by itself, is not allowed but maybe the Insert/Select would be.