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

Amit Mohanty · Answer

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 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. Open SQL Server Management Studio and connect to your SQL Server instance. Expand the "SQL Server Agent" folder in the Object Explorer. Right-click on the "Jobs" folder and select "New Job". This will open the New Job dialog box. In the "General" tab of the New Job dialog box, provide a name for the job in the "Name" field. 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. In the New Job Step dialog box, provide a name for the step in the "Step name" field. In the "Type" section, select "Transact-SQL script (T-SQL)" as the type. In the "Database" field, select the appropriate database where your stored procedure exists. In the "Command" field, enter the code to execute your stored procedure. Here's an example: EXEC usp_DeleteOldRecords; Click on the "OK" button to save the job step. 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. 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. Click on the "OK" button to save the job schedule. Review the job settings in the "Notifications", "Targets", and "History" tabs as per your requirements. Click on the "OK" button to create the job.

Brahma Prakash Shukla · Answer

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)

Naimish Makwana · Answer

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 Thanks

Anandu G Nath · Answer

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 You can use the above query as a refrence, according to your needs you can change.@Meghana M

Prasad Raveendran · Answer

The deletion process can be done in batches using a WHILE loop. Here's how it works: 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. 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. 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. 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 0 BEGIN DELETE TOP (@BatchSize) FROM Table2 WHERE CreatedDate 0 BEGIN DELETE TOP (@BatchSize) FROM Table3 WHERE CreatedDate 0 ROLLBACK; -- Throw the error message THROW; END CATCH END

Janarthanan Selvaraj · Answer

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 -- Delete data from the second table DELETE FROM table2 WHERE date_column -- Delete data from the third table DELETE FROM table3 WHERE date_column -- 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;

Subarta Ray · Answer

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 -- Delete data from Table2 DELETE FROM Table2 WHERE CreatedDate -- 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;

Jayraj Chhaya · Answer

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)