-- =============================================
-- Author:  James Fu
-- Create date: 2015/10/27
-- Description: v0.1    利用批量的方式删除符合条件的数据
-- =============================================
CREATE PROCEDURE [dbo].[sp_LargeDelete]
    @TableName sysname,
    @MaxRows int = 100000,
    @Filter nvarchar(512) = ''
AS
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @SQL nvarchar(512)


    BEGIN TRY
        IF ( @Filter = '' OR @Filter is null )
        BEGIN
            SET @SQL = 'TRUNCATE TABLE '+@TableName ;
            exec sp_executesql @SQL
        END
        ELSE
        BEGIN
            DECLARE @Count INT = -1
            SET @SQL = 'DELETE TOP ('+CAST(@MaxRows AS varchar) + ') FROM  ' + @TableName + ' WHERE ' + @Filter + ' OPTION ( MAXDOP 1 )' ;
            WHILE @Count <> 0
            BEGIN
                BEGIN TRAN
                exec sp_executesql @SQL
                SET @Count = @@ROWCOUNT
                COMMIT
            END
        END
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
        IF @@TRANCOUNT > 0
        ROLLBACK
    END CATCH
END

?