[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