Stored Procedure - Daily Autodelete

-- 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