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