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
from DBC.Tables t
where DatabaseName like '%' || :DName || '%'
and TableName like any ('%_BKP%','%_TMP%','%_BACKUP%')
and CreateTimeStamp < (current_date - interval '1' month;
fetch cname into DatabaseName, Tablename;
set sql_stmt = 'drop table' || :DatabaseName || '.' || :TableName;
prepare stmt1 from sql_stmt;
9261 Stored procedure compiled with empty SPL file.
Help me, please!
Sorry, can't help directly on this one but you may want to post this to the Database channel versus Enterprise as you may find the expertise more so there.