SQL Stored procedure

Question

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

Amit Mohanty · Answer

CREATE PROCEDURE usp_DeleteOldData AS BEGIN DELETE FROM YourTableName WHERE YourDateColumn 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_DeleteOldData; 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.

Janarthanan S · Answer

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 To execute SQL code EXEC DeleteOldData;

Rajkiran Swain · Answer

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

SQL Stored procedure

Answers (3)