The SQL stored procedure deletes data in batches. For more information, see
The SQL stored procedure deletes data in batches. For more information, see
The Code is as follows:
Create PROCEDURE Batch_Delete
@ TableName nvarchar (100), -- table name
@ FieldName nvarchar (100), -- delete the field name
@ DelCharIndexID nvarchar (1000)
As
DECLARE @ PointerPrev int
DECLARE @ PointerCurr int
DECLARE @ TId NVARCHAR (50), @ SQL NVARCHAR (1000)
Set @ PointerPrev = 1
While (@ PointerPrev <LEN (@ DelCharIndexID ))
Begin
Set @ PointerCurr = CharIndex (',', @ DelCharIndexID, @ PointerPrev)
If (@ PointerCurr> 0)
Begin
SET @ TId = cast (SUBSTRING (@ DelCharIndexID, @ PointerPrev, @ PointerCurr-@ PointerPrev) As NVARCHAR (50 ))
SET @ SQL = 'delete from' + @ TableName + 'where' + @ FieldName + '= ''' + @ TID + ''''
Exec (@ SQL)
Print ('========' + @ TId +' ====== SQL '+ @ SQL)
SET @ PointerPrev = @ PointerCurr + 1
Print (@ PointerPrev)
End
Else
Begin
Print ('Break ')
Break
End
End
-- Delete the last one. Because there is no comma after the last one, it jumps out of the loop and needs to be deleted again.
SET @ TId = cast (SUBSTRING (@ DelCharIndexID, @ PointerPrev, LEN (@ DelCharIndexID)-@ PointerPrev + 1) As NVARCHAR (50 ))
SET @ SQL = 'delete from' + @ TableName + 'where' + @ FieldName + '= ''' + @ TID + ''''
Exec (@ SQL)
Print ('========' + @ TId +' ====== SQL '+ @ SQL)
GO
