-- =============================================

-- 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