Auto delete record after expiry period in SQL Server

Hi mahesh213,

In the ExpiryPeriod column save the period in Days (INT) form insterd of week or month. If you save  week or month form then you need to check each possible value to convert it to days.

Refer below test Query and implement as per your table structure.

SQL

DECLARE @Id INT
IF EXISTS(SELECT Id FROM Expiry WHERE Id = @Id)
BEGIN
	DECLARE @Date DATE, @Days INT
	SET @Date = (SELECT [Date] FROM Expiry WHERE Id = @Id)
	SET @Days = (SELECT ExpiryPeriod FROM Expiry WHERE Id = @Id)
	SET @Date = DATEADD(DAY,@Days,@Date)
	IF(@Date >= CONVERT(DATE,GETDATE()))
	BEGIN
		DELETE FROM Expiry WHERE Id = @Id
	END
END