URGENT : Using SELECT statement in dynamic SQL

Database

URGENT : Using SELECT statement in dynamic SQL

Hi ,
I am new to teradata

1) In my procedure i am passing table name (str_sourcetable ) as in parameter
2) Then i need to take the record count from that table (str_sourcetable )
3) This record count should be the field value for another table (Cfstagedev.Table_Test_automation) which is having only one filed
4)I heard that we can not use select statement in dynamic SQL
IS it correct , if so what should be the alternative
I heard that alternative is Volatile tables. So what is a volatile table , how can I implement those inside my procedure
5) There may be other mistakes in my procedure .

As it is a urgent requirement for me , please provide me a correct procedure which will fullfill my requiement and syntatically correct.
Help me out with correct solution

Procedure :
-------------------------

Replace Procedure CFSysSPDev.AutomatedPassthruValidation(In str_sourcetable varchar(2000)
)
BEGIN

Declare v_strsql varchar(6000);
Declare int_recorddiff integer ;

Set v_strsql = 'SELECT COUNT(1) INTO :int_recorddiff FROM ' || str_sourcetable ;

call DBC.SYSEXECSQL(:v_strsql);

Insert Into Cfstagedev.Table_Test_automation
Values ( int_recorddiff);
END;
1 REPLY

Re: URGENT : Using SELECT statement in dynamic SQL

It's true that you cannot use a select statement in dynamic SQL, but you can use a subquery, like this:

set-vstrsql =
'insert into Cfstagedev.Table_Test_automation select count(1) from' || str_sourcetable ;