SQL存储过程将符合条件的大量记录批量删除脚本
程序员文章站
2022-05-28 20:34:00
-- =============================================
-- Author: James Fu
-- Create date:...
-- ============================================= -- 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
上一篇: js实现翻页功能