Creating and populating a parameterized table inside a stored procedure

Database

Creating and populating a parameterized table inside a stored procedure

I am trying to create a table and populate it inside a stored procedure but I get E(3807): table does not exist.

I used the following query:

 create procedure stp_agg (in par1 varchar (20),in par2 int)

begin

    create table tbl_spt

    as

    (select subscriber_id from existing_tbl) with no data;

    insert into tbl_spt (subscriber_id)

    select  subscriber_id from existing_tbl where col1 = par1 and par2 > col2;

end ;

What am I doing wrong?Thank you very much!

Tags (3)
3 REPLIES
Enthusiast

Re: Creating and populating a parameterized table inside a stored procedure

create the table and then compile the procedure

Re: Creating and populating a parameterized table inside a stored procedure

And if I need the table to be created inside the procedure?

Teradata Employee

Re: Creating and populating a parameterized table inside a stored procedure

Convert the INSERT/SELECT to dynamic SQL (EXECUTE IMMEDIATE or CALL DBC.SysExecSQL). Or use CREATE TABLE AS ... WITH DATA.