SQL Stored procedure

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    DECLARE @CurrentDate DATE = GETDATE();
    DECLARE @TargetMonth INT = 6; -- Specify the month for deletion (e.g., June)

    -- Calculate the target date in the specified month
    DECLARE @TargetDate DATE = DATEFROMPARTS(YEAR(@CurrentDate) - 1, @TargetMonth, 1);

    -- Perform the deletion
    IF @CurrentDate >= @TargetDate
    BEGIN
        DELETE FROM YourTable
        WHERE YourDateColumn < DATEADD(YEAR, -1, @CurrentDate);

        -- Optional: You can add more delete statements for other tables if needed
        -- DELETE FROM AnotherTable
        -- WHERE AnotherDateColumn < DATEADD(YEAR, -1, @CurrentDate);
    END
END

To execute SQL code

EXEC DeleteOldData;

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    -- Determine the date range for deletion
    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME

    SET @StartDate = DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1))
    SET @EndDate = DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 31)

    -- Perform deletion based on the date range
    DELETE FROM YourTableName
    WHERE YourDateColumn >= @StartDate AND YourDateColumn <= @EndDate
END

CREATE PROCEDURE usp_DeleteOldData
AS
BEGIN
    DELETE FROM YourTableName
    WHERE YourDateColumn < DATEADD(YEAR, -1, GETDATE())
END;

To run a stored procedure in a specified month after every 1 year, you can use a combination of SQL Server Agent and a scheduled job.

1. Open SQL Server Management Studio and connect to your SQL Server instance.

2. Expand the "SQL Server Agent" folder in the Object Explorer.

3. Right-click on the "Jobs" folder and select "New Job". This will open the New Job dialog box.

4. In the "General" tab of the New Job dialog box, provide a name for the job in the "Name" field.

5. In the "Steps" tab, click on the "New" button to create a new step for the job. This will open the New Job Step dialog box.

6. In the New Job Step dialog box, provide a name for the step in the "Step name" field.

7. In the "Type" section, select "Transact-SQL script (T-SQL)" as the type.

8. In the "Database" field, select the appropriate database where your stored procedure exists.

9. In the "Command" field, enter the code to execute your stored procedure. Here's an example:

   EXEC usp_DeleteOldData;

10. Click on the "OK" button to save the job step.

11. In the "Schedules" tab of the New Job dialog box, click on the "New" button to create a new schedule for the job. This will open the New Job Schedule dialog box.

12. In the New Job Schedule dialog box, configure the schedule as follows:

    • Choose the desired frequency (e.g., "Monthly").
    • Specify the month and day for the job to run.
    • Set the appropriate start date and time.
    • Configure any additional options according to your needs.

13. Click on the "OK" button to save the job schedule.

14. Review the job settings in the "Notifications", "Targets", and "History" tabs as per your requirements.

15. Click on the "OK" button to create the job.