frequently DELETE in Stored Procedure

Question

How I can create a stored procedure and use frequently query like this:

    SET NOCOUNT ON;
    DECLARE @r INT;
    SET @r = 1;
    WHILE @r > 0
    BEGIN
      BEGIN TRANSACTION;
      DELETE TOP (100000)
        dbo.table1
        WHERE Create_Date < DATEADD(YEAR, -5, GETDATE());
      SET @r = @@ROWCOUNT;
      COMMIT TRANSACTION;
      CHECKPOINT;
END

in my new stored procedure? Thanks for Your answers.

Answer 1

You can make your DELETE statements dynamic using something like below:

CREATE PROCEDURE dbo.DeleteRows (
    @tableName VARCHAR(50),
    @timestampColName VARCHAR(100),
    @since DATETIME2,
    @rows INT = 100000
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @r INT;
    SET @r = 1;
    WHILE @r > 0
    BEGIN
       -- SQL injection might be a problem if table and column name are not coming from a trustworthy source (i.e. user input)
       DECLARE @SQL = N'
         DELETE TOP (' + CAST(@Count AS INT) + ')' + @tableName + '
         WHERE ' + @timestampColName + ' < @since;'

      EXEC sp_executesql @SQL, N'@since DATETIME', @since = @since

      SET @r = @@ROWCOUNT;
    END
END

SQL injection can be tackled using one of the techniques indicated in this question and its answers.