stored procedure

UDA
Enthusiast

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!

1 REPLY
Teradata Employee

Re: stored procedure

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.