#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0

    Exclamation Stored Procedure - Daily Autodelete


    I new in the world of Stored Procedures so there it goes:

    I need an Stored Procedure that will automatically delete records that are more than one week old from the db, my date field is called 'ammend'. Eventhough I kind of know how to do the actual procedure, I just want to make sure, or I will erase necessary data accidentaly .

    Also, how do I make it run daily from SQL? .

    Thanks in advance,

    lblock0110
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    NY
    Posts
    18
    Rep Power
    0
    Alright, below is a sample of how you can write the procedure (use the delete statement from the code). I have wrapped everything up in a larger script to show the usage of the DateAdd() function which you will want to use to select the desired records. The script consists of three parts. The first, creates a test table to delete from records with and adds a months worth of records. The second creates the stored procedure used to delete all records over one week old. The final selects the data from the table, calls the delete proc and then selects the data from table again to show the record differences.
    As far as scheduling, just use Sql Agent and set it as a daily job to be executed.

    -- Messorian


    Code:
    -- Create  a test table in tempdb
    
    USE tempdb -- scratchpad :-)
    SET NOCOUNT ON -- disable record counts from being returned (improves performance)
    
    DECLARE @date smalldatetime
    
    -- drop the table if it exists
    IF EXISTS(SELECT * FROM sysObjects WHERE name = 'TestData' AND xtype='U')
    	DROP TABLE TestData
    
    
    -- create a table to test with
    CREATE TABLE TestData(
    	pk int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),
    	myData varchar(128) NULL,
    	ammend smalldatetime
    )
    CREATE NONCLUSTERED INDEX IDX_TESTDATA_ENTDATE
    	ON TestData(ammend) -- helps the performance of the delete statement
    
    	
    	-- today's Date
    	SET @date = getdate() 
    
    	-- loop until the table has been populated with the dates of the last four weeks
    	-- newid() is used just to put in some arbitary data
    	WHILE @date > DateAdd(wk, -4, GetDate()) BEGIN
    		INSERT INTO TestData(myData, ammend)
    			VALUES(newID(), @date)
    
    		-- set @date back one day
    		SET @date = DateAdd(d, -1, @date)
    	END
    GO
    
    
    
    
    
    ---------------------------------------
    ---------------------------------------
    -------- RUN THIS PART SECOND ---------
    ---------------------------------------
    ---------------------------------------
    
    -- STORED PROC CREATION
    IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_DeleteOldEntries' AND xtype='P')
    	DROP PROCEDURE usp_DeleteOldEntries
    GO
    CREATE PROCEDURE usp_DeleteOldEntries
    AS
    	SET NOCOUNT ON
    
    	-- start a transaction to ensure that if something goes wrong, you can revert to the original
    	-- data
    	BEGIN TRANSACTION
    		DELETE FROM TestData 
    			WHERE ammend < DateAdd(wk, -1, GetDate())
    
    	-- if an error occurred roll the tran back (yes, this is not a perfect example of 
    	-- handling errors, but the question wasnt about transaction management ;-)		 )
    	IF @@ERROR != 0 BEGIN
    		ROLLBACK TRANSACTION
    		PRINT 'Mayday!! Mayday!!'
    		RETURN
    	END	ELSE	
    		COMMIT TRANSACTION
    GO
    
    
    
    
    ---------------------------------------
    ---------------------------------------
    -------- RUN THIS PART THIRD  ---------
    ---------------------------------------
    ---------------------------------------
    -- Execute the query and review the results on the table --
    	SELECT * FROM TestData
    
    	exec usp_DeleteOldEntries
    
    	SELECT * FROM TestData

IMN logo majestic logo threadwatch logo seochat tools logo