存储过程部份功能:删除一个表中的重复记录,有ID字段.代码段如下:
declare @表名 varchar(50) --该表有字段:mobile,id(标示字段)
declare @SQLstr varchar(3000)
SET @SQLstr= 'delete from '+ @表名+ ' where id in (select max(id) from '+ @表名+ 'group by mobile having count(*)> 1) '
while exists(select mobile,count(*) from @表名 group by mobile having count(*)> 1 ) --这里的表名怎么处理呢???
begin
EXEC(@SQLCmd3)
end
这个exists怎么处理呢?现在用的是while @@rowcount > 0 来应急的.
------解决方案--------------------
我也遇到这样的问题，也是用@@rowcount处理的
select mobile,count(*) from @表名 group by mobile having count(*)> 1
这个还是拼接SQL串
然后execute 判断@@rowcount
------解决方案--------------------
查询语句中字段或表名是变量的话只能用动态查询(exec),
因为exists不能内嵌exec语句，所以也只能用@@rowcount处理了
------解决方案--------------------
--这个更好一些
declare @表名 varchar(50)
declare @SQLstr varchar(3000)
SET @SQLstr= 'delete from '+ @表名+ ' where id in (select max(id) from '+ @表名+ 'group by mobile having count(*)> 1) '
declare @condition varchar(1000)
set @condition= 'select mobile,count(*) from ' + @表名 + ' group by mobile having count(*)> 1 '
exec(@condition)
while @@rowcount> 0
begin
set nocount on
EXEC(@SQLstr)
set nocount off
exec(@condition)
end