复制代码 代码如下:
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