SQL存储过程将符合条件的大量记要批量删除脚本

-- =============================================-- Author:  James Fu-- Create date: 2015/10/27-- Description: v0.1    利用批量的方式删除符合条件的数据-- =============================================CREATE PROCEDURE [dbo].[sp_LargeDelete]    @TableName sysname,    @MaxRows int = 100000,    @Filter nvarchar(512) = ''ASBEGIN    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 CATCHEND