MsSql教程网-sql 存储过程批量删除数据的语句

复制代码 代码如下:

Create PROCEDURE Batch_Delete

@TableName nvarchar(100), –表名

@FieldName nvarchar(100), –删除字段名

@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

–删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除

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