Create a SP to delete data from SQL table 1

[eo]

SQL Server 2000

Hi,

I have to do a house keeping job, and want to crate a SP which I can run in future to make this manual task easier.

I have a DB with 20 tables, each copntaining potentially millions of records, updated on a daily basis, each record datetime stamped. I only need data in here for the past 45 days, so occasionally want to delete the data from the 20 tables based on a simple query.

Note that we use imperial time formats (non US) yyyy-mm-dd-hh-mm-ss - but not true datatime, but rather nvarchar
For example:

Delete
From AP_Objects
Where AP_Info_Date < '20070501000001'

Delete
from dbo.AP_ReportExtInfo
where AP_Info_Date < '20070501000001'

Delete
from dbo.AP_ServerInfoCache
where AP_UpdateDate < '20070501000001'

Delete
from dbo.AP_ReportExtInfo
where AP_Info_Date > '20070701000001'

As you will see, the date parameter will be the same for all tables, but its description might not be.

I want to create a SP, but replace the data parameter with a SP parameter, but have no idea how to go about doing this. Something like:

Delete
From AP_Objects
Where AP_Info_Date < @Date

Delete
from dbo.AP_ReportExtInfo
where AP_Info_Date < @Date

Delete
from dbo.AP_ServerInfoCache
where AP_UpdateDate < @Date

Delete
from dbo.AP_ReportExtInfo
where AP_Info_Date > @Date

So in future, when I run the SP I can simply say:

Exec NewDeleteSP '20070501000001'

EO
Hertfordshire, England

 

[bborissov]

CREATE PROCEDURE NewDeleteSP(
       @Date datetime
)

AS
 BEGIN
     IF @Date IS NULL
        RETURN -1

     Delete From AP_Objects
            Where AP_Info_Date < @Date

     Delete from dbo.AP_ReportExtInfo
            where AP_Info_Date < @Date

     Delete from dbo.AP_ServerInfoCache
            where AP_UpdateDate < @Date

     Delete from dbo.AP_ReportExtInfo
            where AP_Info_Date > @Date

     RETURN 1
 END

Exec NewDeleteSP '20070501'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mohsinnazir]

You can create a function in sql server e.g

CREATE FUNCTION convertInToDate(@X varchar(20))
RETURNS smalldatetime AS
BEGIN
return cast(SUBSTRING ( '20070501000001' , 1 ,4)+'-'+SUBSTRING ( '20070501000001' , 5 ,2)+'-'+ SUBSTRING ( '20070501000001' , 7 ,2) + ' ' + SUBSTRING ( '20070501000001' , 9,2)+':'+SUBSTRING ( '20070501000001' , 11,2)+':'+SUBSTRING ( '20070501000001' , 13,2) as smalldatetime)

END


and in your query you can do that

Delete
From AP_Objects
Where dbo.convertInToDate(AP_Info_Date) < dbo.convertInToDate(@Date)


you can pass your data string to your main store procedure