Stored proc to remove records older than N days

Question

I want to create a two stored procedures that will remove records older than N days from a stgging database.

Both stored proc will call in a package to cleanup data older than N days.

Stored proc A will remove records from table A.Fullimport & stored proc B will remove records from table B.weeklyimport.

The same stored proc will remove all records from error table where there is not a record in table x and table Y.

Both stored proc will accept input variable @Cleanupdays.

Solution

Basically, you'd have to write something like:

CREATE PROCEDURE dbo.CleanupTableA (@CleanupDays INT)
AS BEGIN  
   DELETE FROM A.Fullimport
   WHERE DATEDIFF(DAY, SomeDateField, GETDATE()) > @CleanupDays
END

or something like that - without the exact table structures, it's really hard to get this totally correct.....

The DATEDIFF function can give you the difference between two dates in various units - e.g. in days, as you want it (could also be weeks, months, year etc.). All you basically have to do is delete all the rows that have a DATEDIFF to today's date that is greater than the value of @CleanupDays passed into the stored procedure.

I don't quite understand what you meant about the error table.... you might need to clarify that a bit...

Comments

[marc_s]

Can you show us the table structures, please??

[TomTom]

Talk to your english teacher about how to formulate a question. This is, by all means, NOT a question. Stating what you want to do is not asking a question.

[Svisstack]

@TomTom: this is her debut on SO;-d

[TomTom]

Does not really change the fact that fgirst year nglish is missing. Even now, after significant changes, i dont see a SINGLE question mark.

[Tony]

You should set up cascading deletes from your FullImport and WeeklyImport tables, then you will not have to explicitly delete rows from the 'errors' table. Although without seeing your table structures it's hard to know if this would work for you.

[JeffO]

@TomTom - Let's assume the question is 'How do I do this.' See punctuation not required.

[TomTom]

Lets dont assume other people are too stupid to now make question marks. I like to take statements as what they are, not to assume they are meant as something different. Lots of people like redefining sentences these days - like "human rights" etc. not me.