SQL stored procedure

Question

SQL Stored procedure to delete multiple tables data from the database older than 1 years. Deletion should be performed in specified month after every 1 year 

Answer 1

  

CREATE PROCEDURE usp_DeleteOldRecords()
BEGIN
    DECLARE @CutoffDate DATETIME;
    SET @CutoffDate = DATEADD(YEAR, -1, GETDATE());

    BEGIN TRY
        BEGIN TRAN
            -- Delete data from Tables
            DELETE FROM Table1 WHERE CreatedDate < @CutoffDate;

            DELETE FROM Table2 WHERE CreatedDate < @CutoffDate;

            DELETE FROM Table3 WHERE CreatedDate < @CutoffDate;
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        -- An error occurred, rollback the transaction
        ROLLBACK;
    END CATCH;
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_DeleteOldRecords;

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.

Answer 2

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    -- Set the specified month and year for deletion
    DECLARE @deletionMonth INT = 6; -- June
    DECLARE @deletionYear INT = YEAR(GETDATE()) - 1; -- One year ago

    -- Disable foreign key checks
    SET FOREIGN_KEY_CHECKS = 0;

    -- Start the deletion process
    BEGIN TRANSACTION;

    -- Delete data from the first table
    DELETE FROM table1
    WHERE YEAR(date_column) < @deletionYear
        OR (YEAR(date_column) = @deletionYear AND MONTH(date_column) <= @deletionMonth);

    -- Delete data from the second table
    DELETE FROM table2
    WHERE YEAR(date_column) < @deletionYear
        OR (YEAR(date_column) = @deletionYear AND MONTH(date_column) <= @deletionMonth);

    -- Delete data from the third table
    DELETE FROM table3
    WHERE YEAR(date_column) < @deletionYear
        OR (YEAR(date_column) = @deletionYear AND MONTH(date_column) <= @deletionMonth);

    -- ... repeat the DELETE statement for other tables ...

    -- Enable foreign key checks
    SET FOREIGN_KEY_CHECKS = 1;

    -- Commit the transaction and make the changes permanent
    COMMIT;
END;

Answer 3

Try below code:

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    -- Variables
    DECLARE @DeletionDate DATE;
    
    -- Set the deletion date to the specified month after every 1 year
    SET @DeletionDate = DATEADD(YEAR, -1, DATEADD(MONTH, 6, GETDATE())); -- Change the month value (6) to the desired month
    
    -- Delete data from Table1
    DELETE FROM Table1
    WHERE DateColumn < @DeletionDate;
    
    -- Delete data from Table2
    DELETE FROM Table2
    WHERE DateColumn < @DeletionDate;
    
    -- Delete data from Table3
    DELETE FROM Table3
    WHERE DateColumn < @DeletionDate;
    
    -- Repeat the delete statements for additional tables
    
    -- Commit the transaction
    COMMIT;
END

Thanks

Answer 4

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

    -- Check if it's the specified month for deletion
    IF MONTH(@CurrentDate) = @TargetMonth
    BEGIN
        DECLARE @CutoffDate DATETIME = DATEADD(YEAR, -1, @CurrentDate); -- Calculate cutoff date

        -- Table 1 deletion
        DELETE FROM Table1
        WHERE DateColumn < @CutoffDate;

        -- Table 2 deletion
        DELETE FROM Table2
        WHERE DateColumn < @CutoffDate;

        -- Add more DELETE statements for other tables as needed
      

        PRINT 'Old data older than 1 year has been deleted.';
    END
    ELSE
    BEGIN
        PRINT 'Not the specified month for data deletion.';
    END
END

 

You can use the above query as a refrence, according to your needs you can change.@Meghana M

Answer 5

The deletion process can be done in batches using a WHILE loop. Here's how it works:

1. The @BatchSize variable determines the number of records to delete in each batch. You can adjust this value based on your requirements and the capacity of your system.
2. After deleting a batch of records from a table, the @@ROWCOUNT function is used to check the number of rows affected by the deletion. If there are still more rows to delete, the loop continues until no more records are left.
3. The DELETE statements for each table are repeated within their respective WHILE loops, ensuring that batches of records are continuously deleted until the condition is met.
4. The transaction is still used to ensure atomicity and handle any errors that may occur during the deletion.

By deleting data in batches, you can manage the impact on system resources and reduce the likelihood of long-running transactions or excessive log growth. Adjust the @BatchSize value based on performance testing and system capacity to find the optimal balance between deletion speed and resource usage.

CREATE PROCEDURE DeleteOldDataInBatches
AS
BEGIN
    DECLARE @BatchSize INT = 10000; -- Number of records to delete in each batch
    DECLARE @CurrentDate DATETIME;
    DECLARE @DeletionMonth INT;
    DECLARE @DeletionYear INT;
    DECLARE @DeletionDate DATETIME;
    DECLARE @RowCount INT;

    -- Set the current date
    SET @CurrentDate = GETDATE();

    -- Set the month for deletion
    SET @DeletionMonth = 2; -- Specify the month for deletion (e.g., February)

    -- Calculate the deletion year
    SET @DeletionYear = YEAR(DATEADD(YEAR, -1, @CurrentDate));

    -- Create the deletion date using the deletion month and year
    SET @DeletionDate = DATEFROMPARTS(@DeletionYear, @DeletionMonth, 1);

    -- Delete data from the tables older than 1 year in batches
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Delete data from Table1
        SET @RowCount = 1;
        WHILE @RowCount > 0
        BEGIN
            DELETE TOP (@BatchSize) FROM Table1
            WHERE CreatedDate < @DeletionDate;

            SET @RowCount = @@ROWCOUNT;
        END

        -- Delete data from Table2
        SET @RowCount = 1;
        WHILE @RowCount > 0
        BEGIN
            DELETE TOP (@BatchSize) FROM Table2
            WHERE CreatedDate < @DeletionDate;

            SET @RowCount = @@ROWCOUNT;
        END

        -- Delete data from Table3
        SET @RowCount = 1;
        WHILE @RowCount > 0
        BEGIN
            DELETE TOP (@BatchSize) FROM Table3
            WHERE CreatedDate < @DeletionDate;

            SET @RowCount = @@ROWCOUNT;
        END

        -- ... (add more DELETE statements for additional tables)

        COMMIT;
    END TRY
    BEGIN CATCH
        -- Handle the error if the deletion fails
        IF @@TRANCOUNT > 0
            ROLLBACK;

        -- Throw the error message
        THROW;
    END CATCH
END

Answer 6

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    -- Check if the current month is January
    IF MONTH(GETDATE()) = 1
    BEGIN
        -- Delete data from the first table
        DELETE FROM table1
        WHERE date_column < DATEADD(YEAR, -1, GETDATE());

        -- Delete data from the second table
        DELETE FROM table2
        WHERE date_column < DATEADD(YEAR, -1, GETDATE());

        -- Delete data from the third table
        DELETE FROM table3
        WHERE date_column < DATEADD(YEAR, -1, GETDATE());

        -- Continue deleting from other tables as needed

        -- Add more DELETE statements for each table you want to delete data from

    END
END
 

 

To execute 

EXEC DeleteOldData;
 

Answer 7

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    DECLARE @CurrentDate DATETIME = GETDATE();
    DECLARE @RetentionDate DATETIME;

    -- Set the retention date to the first day of January of the previous year
    SET @RetentionDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, @CurrentDate) - 1, 0);

    -- Check if the current month is January before proceeding with deletion
    IF MONTH(@CurrentDate) = 1
    BEGIN
        -- Delete data from Table1
        DELETE FROM Table1 WHERE CreatedDate < @RetentionDate;

        -- Delete data from Table2
        DELETE FROM Table2 WHERE CreatedDate < @RetentionDate;

        -- Add more tables as needed

        PRINT 'Data older than 1 year deleted successfully.';
    END
    ELSE
    BEGIN
        PRINT 'Deletion is only performed in January.';
    END
END;
 

Answer 8

Please refer this one

CREATE PROCEDURE DeleteOldData
AS
BEGIN
    DECLARE @CurrentDate DATE = GETDATE();
    DECLARE @TargetDate DATE = DATEADD(YEAR, -1, @CurrentDate);
    DECLARE @TargetMonth INT = MONTH(@TargetDate);

    -- Table 1
    DELETE FROM Table1
    WHERE YEAR(DateColumn) <= YEAR(@TargetDate)
    AND MONTH(DateColumn) = @TargetMonth;

    -- Table 2
    DELETE FROM Table2
    WHERE YEAR(DateColumn) <= YEAR(@TargetDate)
    AND MONTH(DateColumn) = @TargetMonth;

    -- Table 3
    DELETE FROM Table3
    WHERE YEAR(DateColumn) <= YEAR(@TargetDate)
    AND MONTH(DateColumn) = @TargetMonth;

    -- Add more tables as needed

END