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.

You want to frequently delete 100,000 records? You might want to look into maintenance plans and agent jobs. — Forklift, Mar 24 '17 at 13:12
What is the actual pattern you are looking for? What do your delete statements have in common? (e.g. different table names, different timestamp column name, but the same mechanism) — Alexei - check Codidact, Mar 24 '17 at 13:13
Why is transaction needed? Single `DELETE` statement does not need that. — Alexei - check Codidact, Mar 24 '17 at 13:13
What is your actual question? How to create a stored procedure, or how to delete efficiently? — Peter Henell, Mar 24 '17 at 13:15

score 1 · Accepted Answer · edited May 23 '17 at 12:00

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.

edited May 23 '17 at 12:00

Community

1
1

answered Mar 24 '17 at 13:18

Alexei - check Codidact

22,016
16
145
164

I need create one stored procedure to frequently delete data in different tables and I can use the same mechanism. – Wiktor Kowalczyk Mar 24 '17 at 13:39
@WiktorKowalczyk - yes, you can create a separate procedure from the proposed code. I have edit it to actually be inside a stored procedure. – Alexei - check Codidact Mar 24 '17 at 13:42

frequently DELETE in Stored Procedure

1 Answers1