SQL SERVER通用分批删除存储过程

若水
2026-01-10 05:32:12
技术
MYSQL/MSSQ

-- 过程参数调用方法

EXEC dbo.usp_DeleteBatchByCondition
    @TableName = 'TBLESIItemLogLot',
    @WhereClause = 'MaxInTime < ''2025-01-01''',
    @BatchSize = 5000,
    @MaxTotalDeletes = 500000;
    @DelayBetweenBatches = '00:00:01';

-- 字符串中的单引号需要双写（''2025-01-01''），这是 T-SQL 字符串规则。

-- 通用分批删除存储过程

CREATE PROCEDURE dbo.usp_DeleteBatchByCondition
    @TableName NVARCHAR(256),             -- 表名，格式：[schema].[table] 或 [table]
    @WhereClause NVARCHAR(MAX),           -- WHERE 条件（不含 "WHERE" 关键字，例如 "MaxInTime < ''2025-01-01''"）
    @BatchSize INT = 5000,                -- 每批删除行数
    @MaxTotalDeletes BIGINT = NULL,       -- 最大总删除行数（可选）
    @DelayBetweenBatches VARCHAR(12) = '00:00:03' -- 批次间隔
AS
BEGIN
    SET NOCOUNT ON;

    -- 参数校验
    IF @BatchSize <= 0 
    BEGIN
        RAISERROR('Parameter @BatchSize must be greater than 0.', 16, 1);
        RETURN;
    END

    IF NULLIF(LTRIM(RTRIM(@TableName)), '') IS NULL
    BEGIN
        RAISERROR('Parameter @TableName cannot be empty.', 16, 1);
        RETURN;
    END

    IF NULLIF(LTRIM(RTRIM(@WhereClause)), '') IS NULL
    BEGIN
        RAISERROR('Parameter @WhereClause cannot be empty.', 16, 1);
        RETURN;
    END

    -- 验证表是否存在（防止误删或注入）
    DECLARE @FullTableName NVARCHAR(256);
    DECLARE @ObjectId INT;

    -- 尝试解析为两部分（schema.table）或单部分（table）
    IF CHARINDEX('.', @TableName) > 0
    BEGIN
        SET @FullTableName = QUOTENAME(PARSENAME(@TableName, 2)) + '.' + QUOTENAME(PARSENAME(@TableName, 1));
    END
    ELSE
    BEGIN
        SET @FullTableName = QUOTENAME(@TableName);
    END

    SET @ObjectId = OBJECT_ID(@FullTableName, 'U');
    IF @ObjectId IS NULL
    BEGIN
        RAISERROR('Table [%s] does not exist or is not a user table.', 16, 1, @TableName);
        RETURN;
    END

    -- 构建动态 SQL 模板（注意：@WhereClause 必须由调用方保证安全！）
    DECLARE @Sql NVARCHAR(MAX);
    DECLARE @RowsDeleted INT = 1;
    DECLARE @TotalDeleted BIGINT = 0;

    -- 提示信息
    PRINT 'Starting batch delete on table: ' + @FullTableName;
    PRINT 'Using condition: ' + @WhereClause;

    WHILE @RowsDeleted > 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            -- 构造 DELETE 语句
            SET @Sql = N'DELETE TOP (' + CAST(@BatchSize AS NVARCHAR(10)) + N') FROM ' 
                     + @FullTableName 
                     + N' WHERE ' + @WhereClause + N';';

            -- 执行动态删除
            EXEC sp_executesql @Sql;

            SET @RowsDeleted = @@ROWCOUNT;
            SET @TotalDeleted += @RowsDeleted;

            COMMIT TRANSACTION;

            -- 检查最大删除限制
            IF @MaxTotalDeletes IS NOT NULL AND @TotalDeleted >= @MaxTotalDeletes
            BEGIN
                PRINT 'Reached MaxTotalDeletes limit (' + CAST(@MaxTotalDeletes AS VARCHAR) + '). Stopping.';
                BREAK;
            END

            -- 延迟与进度
            IF @RowsDeleted > 0
            BEGIN
                PRINT CAST(@RowsDeleted AS VARCHAR) + ' rows deleted in this batch. Total so far: ' + CAST(@TotalDeleted AS VARCHAR);
                WAITFOR DELAY @DelayBetweenBatches;
            END

        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
            DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
            DECLARE @ErrorState INT = ERROR_STATE();
            RAISERROR('Error during batch delete: %s', @ErrorSeverity, @ErrorState, @ErrorMessage);
            RETURN;
        END CATCH
    END

    PRINT 'Deletion completed. Total rows deleted from ' + @FullTableName + ': ' + CAST(@TotalDeleted AS VARCHAR);
END

本文为宁若水!原创文章,转载无需和我联系,但请注明来自[若水]博客 www.lalaya.net