mysql存储过程删除大量数据_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) = ''

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