All Forums Database
jerboa 10 posts Joined 04/12
28 Apr 2012
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))


declare DatabaseName varchar (128);

declare TableName varchar (128);

declare sql_stmt varchar (100);

declare cname cursor for

select t.DatabaseName,



  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;



9261 Stored procedure compiled with empty SPL file.

Help me, please!

You must sign in to leave a comment.