All Forums Database
17 Apr 2015
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!

anujh 22 posts Joined 05/11
17 Apr 2015

create the table and then compile the procedure

22 Apr 2015

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

Fred 1096 posts Joined 08/04
28 Apr 2015

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

You must sign in to leave a comment.