teL3XZji

1Date   Name    Id
2	
3--Create a test demo table and insert a couple of rows
4IF OBJECT_ID('dbo.TestDelete', 'U') IS NOT NULL 
5  DROP TABLE dbo.TestDelete; 
6 go
7create table TestDelete (ID int, Name varchar(100), DateOnTable Date)
8insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-01-01')
9insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-03-01')
10go
11	
12--Drop procedure if is already exists
13IF EXISTS (
14        SELECT *
15        FROM dbo.sysobjects
16        WHERE id = object_id(N'[dbo].[DeleteTableRowsLessThanSpecificDate]')
17            AND OBJECTPROPERTY(id, N'IsProcedure') = 1
18        )
19    DROP PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate]
20GO
21
22--Create the procedure
23create PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate] @DeleteDate DATE
24    ,@SchemaName SYSNAME
25    ,@TableName SYSNAME
26AS
27BEGIN
28    SET NOCOUNT ON
29    SET XACT_ABORT ON
30
31    --Example error checking to prevent deleting rows with a date within the last month
32    IF @DeleteDate >= Dateadd(MONTH, - 1, sysdatetime())
33    BEGIN
34        RAISERROR ('Delete Date Too Recent - Delete cancelled',16,1);
35
36        RETURN
37    END
38
39    --Declare a variable to hold the dynamic SQL command
40    DECLARE @Command NVARCHAR(4000)
41    DECLARE @ParmDefinition NVARCHAR(4000) = '@SchemaName sysname, @TableName sysname, @DeleteDate date'
42
43    --Build the command by concatenating the input TableName and input DeleteDate
44    SET @Command = 'DELETE FROM ' + quotename(@SchemaName) + '.' + quotename(@TableName) + ' WHERE DateOnTable < ''' + convert(VARCHAR(20), @DeleteDate) + ''''
45
46    --Printing just to verify the syntax
47    PRINT @command
48
49    --Dynamically execute the command you just built
50    --(Using EXEC() instead of sp_executesql)
51    --Check this blog post about why you should use sp_executesql instead of EXEC()
52    --https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql
53    EXECUTE sp_executesql @Command
54        ,@ParmDefinition
55        ,@SchemaName = @SchemaName
56        ,@TableName = @TableName
57        ,@DeleteDate = @DeleteDate;
58END
59go
60	
61--Now, we're ready to test the stored procedure by deleting all rows
62--less than 2017-04-08.  That should leave only one row in our example (2017-06-01)
63
64--Check rows before calling the stored procedure
65select * from TestDelete
66
67--Call the stored procedure
68EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-02-01'
69    ,@SchemaName = 'dbo'
70    ,@TableName = 'TestDelete'
71
72--Check rows after calling the stored procedure
73select * from TestDelete
74	
75--Testing error logic by trying to delete rows within the last month
76EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-04-01'
77    ,@SchemaName = 'dbo'
78    ,@TableName = 'TestDelete'