| 解决方案： |
因为GL_ACCSUM表中有丢失的记录，用下面的脚本创建存储过程，再执行此存储过程。
---解决gl_accsum中缺少记录导致综合明细帐查询错误等其他问题。
--应在创建完过程后用EXEC执行过程。EXEC MYPROC1就可以解决了。
if Exists (Select Name From sysobjects
where name=‘myproc1‘ and type=‘P‘)
begin
DROP PROCEDURE MYPROC1
end
GO
CREATE PROCEDURE myproc1 AS
set nocount on
--检查临时表是否存在
if Exists (Select Name From tempdb..sysobjects
where name=‘tmp1‘ and type=‘U‘)
begin
DROP table tempdb..tmp1
end
--筛选出有丢失记录的数据,并存入临时表tmp1
SELECT ccode,max(iperiod)as maxmonth
into tempdb..tmp1 FROM GL_accsum
GROUP BY GL_accsum.ccode
HAVING ((Max(GL_accsum.iperiod))<12)
--在缺少记录的集合中定义游标
Declare sCursor Cursor FOR
Select ccode,maxmonth
From tempdb..tmp1
OPEN sCursor
declare @ccode varchar(15),
@iperiod tinyint,@mb money,@i_period tinyint,@h_q varchar(500),@h_q1 varchar(500),@h_q2 varchar(500)
FETCH NEXT FROM sCursor
Into @ccode,@iperiod
While @@FETCH_STATUS=0
begin
set @i_period=@iperiod
--检查临时表是否存在
if Exists (Select Name From tempdb..sysobjects
where name=‘tmp2‘ and type=‘U‘)
begin
DROP table tempdb..tmp2
end
--从源表中找到丢失记录的最大的月份的记录,并写入临时表tmp2
select * into tempdb..tmp2
from gl_accsum
where ccode
=@ccode
and iperiod=@iperiod
while @i_period<12
begin
set @i_period=@i_period+1
set @h_q=‘update tempdb..tmp2 set iperiod=iperiod+1‘
set @h_q1=‘update tempdb..tmp2 set mb=me,mb_f=me_f,nb_s=ne_s‘
set @h_q2=‘update tempdb..tmp2 set mc=0,md=0,md_f=0,mc_f=0,nd_s=0,nc_s=0‘
EXEC(@h_q)
EXEC(@h_q1)
EXEC(@h_q2)
insert gl_accsum (ccode,cexch_name,iperiod,
cbegind_c,cbegind_c_engl,mb,md,mc,me,cendd_c,cendd_c_engl,mb_f,md_f,mc_f,me_f,nb_s,nd_s,nc_s,ne_s)
select ccode,cexch_name,iperiod, cbegind_c,cbegind_c_engl,mb,md,mc,me,cendd_c,cendd_c_engl,mb_f,md_f,mc_f,me_f,nb_s,nd_s,nc_s,ne_s
from tempdb..tmp2
end
FETCH NEXT FROM sCursor Into @ccode,@iperiod
end
--关闭并释放游标
CLOSE sCursor
DEALLOCATE sCursor
--SELECT COUNT(*) FROM GL_ACCsum
--执行过程
--EXEC MYPROC1 |