ERDS网校 - ERDS企业自主信息化培训,交流

查看: 362|回复: 0

删除数据

[复制链接]

80

主题

87

帖子

315

积分

版主

Rank: 7Rank: 7Rank: 7

积分
315
发表于 2015-11-27 11:09:40 | 显示全部楼层 |阅读模式
--清空数据报表   
alter proc data_report(@type varchar(10),@name varchar(100))      
as      
set nocount on      
begin      
--declare @type varchar(10),@name varchar(100)      
select bh 编号,des 表名称,[type] 类别,(case when rows=0 then '无数据' else '有数据' end) 是否有数据 from      
(      
select distinct 'ZSJ-'+cast(dfn.id as varchar) bh,'主数据' [type],DES,rows from dfn left      
join dmdc on dmdc.tid=dfn.id  
inner join
(SELECT top 9999999 a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC)c
on tname=[name]
where tname  not in (select distinct dtable from dfd where dtable like '%_bak')        
and tname not like '%_bak'      
and isnull(dfn.applyid,0) not in(select pid from yb_apply where Is_Enable='否')      
and (isnull(@type,'')=''  or @type ='主数据' )      
and (isnull(@name,'')=''  or des like '%'+@name+'%')      
and  ddto='ParentTable'      
union      
select 'LC-'+cast(a.fid as varchar),'流程',fname,rows
from  dflow a with(nolock) inner join yb_apply b with(nolock) on a.Applyid=b.pid
inner join
(select fid,sum(rows) rows from
  (select distinct b.wtname,a.fid from DFSP a inner join dwd b on a.cid=b.wid
   union   
   select distinct b.wtname,a.fid from dstep a inner join dwd b on a.wid=b.wid
   union   
   select distinct b.wtname + '_bak',a.fid from DFSP a inner join dwd b on a.cid=b.wid )a
   inner join
  (SELECT top 999999999 a.name, b.rows
   FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
   WHERE (a.type = 'u') AND (b.indid IN (0, 1))
   ORDER BY a.name,b.rows DESC)b
   on a.wtname=b.[name]
   group by fid)c
on   a.fid=c.fid
where b.Is_Enable='是'      
and (isnull(@type,'')=''  or @type ='流程' )      
and (isnull(@name,'')=''  or fname like '%'+@name+'%'))a


end



--删除数据
alter proc data_delete
@eid VARCHAR(300)
as
set nocount on
begin
--定义变量表存储要清空的主数据
declare @zsj table (id int identity(1,1),tname varchar(100))
--插入主数据主表的表名
insert into @zsj(tname)
select distinct tname from dfn
where ('ZSJ-'+cast(dfn.id as varchar) IN (SELECT F1 FROM dbo.split(@eid,'|')))
--插入主数据主表的表名
insert into @zsj(tname)
select tname from DFN where pid in (select a.id from dfn a inner join @zsj b on a.tname=b.tname)

--定义变量存储清空语句
declare @str varchar(max)
declare @i int
set @i=1
while (@i<=(select max(id) from @zsj))
  begin
   set @str='truncate table '+(select tname from @zsj where id=@i)
   exec (@str)
   set @i=@i+1
  end

--定义变量表存储要清空的流程号
declare @lc_fid table(id int identity(1,1),fid int)
insert into @lc_fid(fid)
select fid from  dflow where 'LC-'+cast(fid as varchar) IN (SELECT F1 FROM dbo.split(@eid,'|'))
--定义变量表存储要清空的流程表
declare @lc_table table(id int identity(1,1),tname varchar(100))
insert into @lc_table(tname)
select distinct b.wtname from DFSP a inner join dwd b on a.cid=b.wid where a.fid in (select fid from @lc_fid) and a.wid<>0
union
select distinct b.wtname from dstep a inner join dwd b on a.wid=b.wid where a.fid in (select fid from @lc_fid) and a.wid<>0
union
select distinct b.wtname + '_bak' from DFSP a inner join dwd b on a.cid=b.wid where a.fid in (select fid from @lc_fid) and a.wid<>0 and isnull(wtype,0)=1
--清空流程表数据
set @i=1
while (@i<=(select max(id) from @lc_table))
begin
  set @str='truncate table '+(select tname from @lc_table where id=@i)
  exec (@str)
  set @i=@i+1
end
--删除数据
delete dfq where fid in (select fid from @lc_fid);
delete dft where fid in (select fid from @lc_fid);
set @i=1
while (@i<=(select max(id) from @lc_table))
begin
  set @str='truncate table dbh_'+(select fid from @lc_fid where id=@i)
  exec (@str)
  set @i=@i+1
end

end



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|ERDS网校   点击这里给我发消息

快速回复 返回顶部 返回列表