SQL Server T-SQL – Writing a Stored Procedure for Deleting Table Rows

sql servert-sql

I face a problem while writing a stored procedure. I want to write a stored procedure so that it gets 2 input parameters (Date and TableName) and then delete table rows which Date column of the table is smaller than the given input date.

The table has 3 columns:

Date   Name    Id

Thank you

Best Answer

Create a stored procedure that builds a dynamic SQL statement based on your input parameters and then executes the command

First, let's create a test table with some data to play with.

 --Create a test demo table and insert a couple of rows
IF OBJECT_ID('dbo.TestDelete', 'U') IS NOT NULL 
  DROP TABLE dbo.TestDelete; 
 go
create table TestDelete (ID int, Name varchar(100), DateOnTable Date)
insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-01-01')
insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-03-01')
go

Now, let's create the 'delete' stored procedure. My example takes in 3 parameters. DeleteDate, SchemaName and TableName. 

--Drop procedure if is already exists
IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE id = object_id(N'[dbo].[DeleteTableRowsLessThanSpecificDate]')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1
        )
    DROP PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate]
GO

--Create the procedure
create PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate] @DeleteDate DATE
    ,@SchemaName SYSNAME
    ,@TableName SYSNAME
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON

    --Example error checking to prevent deleting rows with a date within the last month
    IF @DeleteDate >= Dateadd(MONTH, - 1, sysdatetime())
    BEGIN
        RAISERROR ('Delete Date Too Recent - Delete cancelled',16,1);

        RETURN
    END

    --Declare a variable to hold the dynamic SQL command
    DECLARE @Command NVARCHAR(4000)
    DECLARE @ParmDefinition NVARCHAR(4000) = '@SchemaName sysname, @TableName sysname, @DeleteDate date'

    --Build the command by concatenating the input TableName and input DeleteDate
    SET @Command = 'DELETE FROM ' + quotename(@SchemaName) + '.' + quotename(@TableName) + ' WHERE DateOnTable < ''' + convert(VARCHAR(20), @DeleteDate) + ''''

    --Printing just to verify the syntax
    PRINT @command

    --Dynamically execute the command you just built
    --(Using EXEC() instead of sp_executesql)
    --Check this blog post about why you should use sp_executesql instead of EXEC()
    --https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql
    EXECUTE sp_executesql @Command
        ,@ParmDefinition
        ,@SchemaName = @SchemaName
        ,@TableName = @TableName
        ,@DeleteDate = @DeleteDate;
END
go

Now, let's test the stored procedure by passing in some different DeleteDates and checking the results.

--Now, we're ready to test the stored procedure by deleting all rows
--less than 2017-04-08.  That should leave only one row in our example (2017-06-01)

--Check rows before calling the stored procedure
select * from TestDelete

--Call the stored procedure
EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-02-01'
    ,@SchemaName = 'dbo'
    ,@TableName = 'TestDelete'

--Check rows after calling the stored procedure
select * from TestDelete

I also added some error checking logic in the stored procedure to show how you might prevent rogue deletes that are too aggressive due to the date being passed in. My example stored procedure throws an error if you try to delete any rows within the past month. (I wrote this answer on 4/9/2017 and on that date, the following throws an error.

--Testing error logic by trying to delete rows within the last month
EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-04-01'
    ,@SchemaName = 'dbo'
    ,@TableName = 'TestDelete'

Msg 50000, Level 16, State 1, Procedure DeleteTableRowsLessThanSpecificDate, Line 14 [Batch Start Line 72] Delete Date Too Recent - Delete cancelled

Related Question