how can i write storec proc which to remove old records older than 90 days from table1 and table2

Question

Hi, i wanna write stored proc which would delete records from dirstaging.trr.table1 and dirstaging.trr.table2 older than 90 days. the same stored proc will remove all records from dir.trr.ErrorTable where there is not a record in dir.trr.table3 and dir.trr.table4. this proc will accept variable @cleanup.

NOTE : i tried something like select all records from table1 and table2 and put them in temp table, truncate table1 and 2 and apply condition on temp table to get latest records and move them in table1 and 2.

Can i do it different way?

Thanks

Answer 1

Since you've not given an ddl I have no way of know how the tables join and what the types are, this is a sample of how I would do it.

   create procedure myProc(@cleanup some_type) as
    begin
       delete dirstaging.trr.table1 
       where the_column < getdate() - 90

       delete dirstaging.trr.table2
       where the_column < getdate() - 90

       -- join error table and table 3 and table 4 and where the rows exist in 3 & 4 delete from e

       delete e
       from dir.trr.ErrorTable e
       left outer join dir.trr.table3 t3 on e.common_column = t3.common_column 
       left outer join dir.trr.table4 t4 on e.common_column = t4.common_column 
       where t3.common_column is null
        or t4.common_column is null 

    end