dinamic SQL, stored procedure

Database
Enthusiast

dinamic SQL, stored procedure


Hi everyone!


I want to write SP for removing tables. The first need to find these tables (they need satisfy some conditions)  and  the second - remove it. I don't know how pass the list of tables to variables for removing theirs. And some errors occurred  during compilation.

create procedure drop_tt (DName VARCHAR(128))

  begin

declare DatabaseName varchar (128);

declare TableName varchar (128);

declare sql_stmt varchar (100);

declare cname cursor for

select t.DatabaseName,

       t.TableName

   

  from DBC.Tables t 

 where DatabaseName like '%' || :DName || '%' 

   and  TableName like any ('%_BKP%','%_TMP%','%_BACKUP%')

   and  CreateTimeStamp < (current_date - interval '1' month;

open cname;

fetch cname into DatabaseName, Tablename;

set sql_stmt = 'drop table' || :DatabaseName || '.' || :TableName;

prepare stmt1 from sql_stmt;

execute stmt1;

close cname;

end;

9261 Stored procedure compiled with empty SPL file.


Help me, please!