前面的文章里写过一个通过修改里面的代码,可以删除特定表里重复数据的存储过程,现在写了一个通用的存储过程,使用表名和列名作为参数,大家有机会给我挑挑毛病。SQL语言这东西真是博大精深,可能不少牛人一句话就解决这问题了,不需要我这么麻烦。
create proc delRptRec @tblName varchar(1024),@pk_column varchar(1024) as declare @errmsg varchar(1000) declare @sql_variant sql_variant declare @sql_delete nvarchar(1024) declare @sql_insert nvarchar(1024) declare @rowcount int declare @sql_set_value nvarchar(1024) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temSurTbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temSurTbl] if @@error=0 print 'temsurtbl is dropped' exec('select * into temsurtbl from ' +@tblname+' where 1=0') --创建新表的同时将源表的结构复制过去 if @@error=0 print 'temsurtbl is created' if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temtbl_pk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temtbl_pk] if @@error=0 print 'temtbl_pk is dropped' --这里使用事务,防止数据全部丢失,最坏的打算 begin transaction --首先将要作为主键的字段的没有重复的记录复制到表temtbl_pk里 exec ('select distinct ' + @pk_column + ' into temtbl_pk from '+@tblName) set @rowcount=@@rowcount--记下有多少个非重复值,为下面的循环做准备 if @@error=0 print 'temtbl_pk is created' print '1 rowcount is '+ cast(@rowcount as varchar) while @rowcount>0 begin set @sql_set_value=N'select top 1 @sql_variant =' + @pk_column +' from temtbl_pk' exec sp_executesql @sql_set_value,N'@sql_variant sql_variant output',@sql_variant output set @sql_delete=N'delete from temtbl_pk where ' + @pk_column + '= @sql_variant' exec sp_executesql @sql_delete,N'@sql_variant sql_variant',@sql_variant --之后将表里的非重复数据复制到另一个中介表里 set @sql_insert=N'insert into temsurtbl select top 1 * from '+@tblName + ' where ' + @pk_column + '= @sql_variant' exec sp_executesql @sql_insert,N'@sql_variant sql_variant',@sql_variant set @rowcount=@rowcount-1 end --将源表里的记录删除 exec ('delete from '+ @tblname) if @@error<>0 begin set @errmsg=@errmsg+'源表不能删除; ' goto problem end --将中介表里的数据复制到源表 exec ('insert into '+@tblName+' select * from temsurtbl') --干完活擦屁股 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temSurTbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temSurTbl] if @@error=0 print 'temsurtbl is dropped' if @@error=0 print 'temsurtbl is created' if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temtbl_pk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temtbl_pk] if @@error=0 print 'temtbl_pk is dropped' --查询删除了重复数据的表里的数据 exec ('select * from '+@tblname) commit transaction print '操作成功' return 0--这里会退出过程,所以其它工作要在这句之前完成 problem: print '操作失败' print @errmsg rollback transaction return 1
里面的关键就是exec sp_executesql 这个东西。