SQL参数问题

[nebuung]

我要对固定的多个tablename进行删除操作，怎么设置类似集合的参数让它每个表全部执行一遍@sql。
tablename=Z_001 ; Z_002 ; Z_003````Z_099
代码如下：
declare @tablename varchar(20)
declare @sql nvarchar(2000)
set @tablename='Z_001'
set @sql=N'delete from ' +@tablename+ '
where ftime in (select ftime from ' +@tablename+ ' group by ftime having count(ftime) > 5)
and id not in (select MIN(id) from ' +@tablename+ ' group by ftime having count(ftime) > 5)'
exec sp_executesql @sql

[丰云]

批处理，了解下。。。。

[二月十六]

把数据库表字符串进行分隔，然后循环处理

[nebuung]

引用 2 楼 sinat_28984567 的回复:

把数据库表字符串进行分隔，然后循环处理

感谢回答，问题已解决，代码有需要优化的没。 declare @tablename varchar(20) declare @sql nvarchar(2000) declare @i int set @i= 0 while @i<76 begin set @i=@i+1 if(@i<10) begin set @tablename='Z_00'+convert(nvarchar(10),@i)+'' end if(@i>=10 and @i<76) begin set @tablename='Z_0'+convert(nvarchar(10),@i)+'' end set @sql=N'delete from ' +@tablename+ ' where ftime in (select ftime from ' +@tablename+ ' group by ftime having count(ftime) > 5) and id not in (select MIN(id) from ' +@tablename+ ' group by ftime having count(ftime) > 5)' exec sp_executesql @sql end

[吉普赛的歌]

不需要那么复杂：

exec sp_MSforeachtable 
@whereand=' and o.name like "Z_00%"'
,@command1='delete from ? 
            where ftime in (select ftime from ? group by ftime having count(ftime) > 5) 
			and id not in (select MIN(id) from ? group by ftime having count(ftime) > 5)'

[nebuung]

引用 4 楼 yenange 的回复:

不需要那么复杂：

exec sp_MSforeachtable 
@whereand=' and o.name like "Z_00%"'
,@command1='delete from ? 
            where ftime in (select ftime from ? group by ftime having count(ftime) > 5) 
			and id not in (select MIN(id) from ? group by ftime having count(ftime) > 5)'

感谢回答，回头再证。