I need a stored procedure to delete bulk data in a loop so that I should not manually delete it. And I want to give the table name (from which the data is to be deleted) as a parameter. And after each loop the number of deleted data is to be printed. Please help me in making a stored proc to do this action.

CodePudding user response：

Here's an example where I converted all the nested subqueries into JOINs. This code is untested since you didn't post any sample data.

CREATE PROCEDURE dbo.testDynamicDelete
  @TableName nvarchar(50)
  , @BatchSize int = 100000
AS
BEGIN
  --Variable to output the number of rows updated.
  DECLARE @rowsDeleted int = 9999;
  DECLARE @totalDeleted int = 0;

  --Check if table name is valid to prevent SQL injection.
  IF !EXISTS(
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG=@TableName
  )
  RETURN;

  WHILE (@rowsDeleted > 0)
  BEGIN
    --Variable to hold the dynamic sql to be executed.
    DECLARE @sqlText nvarchar(max) = '
      DELETE TOP ('   CAST(@BatchSize as nvarchar(10))   ')
      FROM '   @TableName   '
      WHERE ID in (
        SELECT ID
        FROM report as r
          INNER JOIN groups as g
            ON g.tripID = r.tripID
          INNER JOIN merchs as m
            ON m.castID = g.castID
          INNER JOIN cls as c
            ON c.clID = m.clID
            AND clID in (123, 456, 789)
      );
    ';

    --Execute the dynamic SQL.
    EXEC (@sqlText);

    --Get the number of rows deleted. Must be done immediately after the execute.
    SET @rowsDeleted = @@ROWCOUNT;

    --Pring the number of rows deleted for feedback.
    PRINT 'Records Deleted: '   CAST(@rowsDeleted as nvarchar);

    --Update the total number of records deleted.
    SET @totalDeleted = @totalDeleted   @rowsDeleted;
  END 

  --Print the total records deleted to indicate end of process.
  PRINT 'Process Complete. Total Deleted: '   CAST(@totalDeleted as nvarchar);
END