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

原标题：SQL存储过程将符合条件的大量记录批量删除脚本

关键词：sql