[SQL][SCRIPT]簡化大量刪除資料的指令

[SQL][SCRIPT]簡化大量刪除資料的指令

原本分享一個處理大量刪除資料所造成的問題 ( [SQL]解決大量資料刪除,造成資料庫交易紀錄檔案容量過大且耗費時間之處理 ),很出乎我意料之外的,居然引起不少的共鳴。本來希望將這樣的處理,在一些系統上可以利用這樣的方式來做改善,但有些時候最難改變的還是人,有不少朋友覺得雖然看起來不錯,但會覺得要改既有的程式很麻煩,要增加不少的判斷和處理。因此我把前面那篇的指令整理一下,把它包裝到 Stored Procedure,這樣如果要改的話,則把原來的 DELETE 的 DML 改成呼叫 Stored Procedure,或許會簡單一點吧。

 


-- =============================================
-- Author:  James Fu
-- Create date: 2015/05/17
-- 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