写了一个动态生成临时表的存储过程,大家看看有没有bug

chpeng 2003-07-14 12:53:14

CREATE PROCEDURE Tech_CnstScore
@Xbid varchar(20)='Y0000048',
@EntList varchar(8000)=''--该参数是为了跟前台的DbTreeList对应
AS
declare @str varchar(8000),@tb varchar(8000),@v varchar(8000),@r varchar(100),@strtmp varchar(8000),@nZj int,
@set varchar(8000),@nCnt int,@slt varchar(100),@Strfmt varchar(8000),@fs float,@Flag int,@nCount int,@fmax float

--初始化
set @tb=''
set @v=''
set @r=''
set @set=''
set @slt=''
set @strtmp=''
select @nZj=count(*)
from tblZJK_Meeting
where Fxbid=@Xbid and FInMeeting='1'

select @fmax=IsNull(FHeightScore,0)
from tblcfg_tech
where fxbid=@Xbid and Fparentid=-1

set @tb='create table #t(FID int,FParnetID int,FNodeType int,FItem varchar(20),FHeightScore float'

set @slt = ' Select * from #t'

--预算条件
declare @Reason varchar(100),@ScoreLine float

select @Reason=IsNull(FlowRejectReason,'')
from tblSys_config

select @ScoreLine=IsNull(FTechLine,0)
from tblBid_EvaGlobalItem
where FXBid=@Xbid

--统计投标企业个数
select @nCnt=count(*)
from tblBid_XBEnterprise
where FXBID=@XBID and FTYPE>0
--set @nCnt=75
--取当前项目参加统计分数,过虑掉不参加统计的数据
declare @t table(Fid int,Expert varchar(20),Entid varchar(20),DecScore float,Score float)

insert into @t
select a.Fid,FExpertID,FEnterpriseID,FDecScore,FScore
from tblBid_TechItemScore a,tblCfg_Tech b
where a.FXBID=@xbid and a.Fid=b.Fid and b.Fxbid=@xbid and b.FSumType='1'

--取当前项目全局变量,去掉最高、最低数和统计类型
declare @high int,@low int,@Class bit

select @high=IsNull(FTechRemoveHigh,0),@low=IsNull(FTechRemoveLow,0),@Class=IsNull(FTechRemoveClass,'1')
from tblBid_EvaGlobalItem
where FXbid=@xbid

--临时存放表
declare @grp table(Expert varchar(20),Score decimal(10,2))--分组统计数据
create table #sort(nid int identity(1,1),Score decimal(10,2))--排序后数据
declare @ent varchar(20),@fid int

--处理后的数据放入该表
create table #s(FId int,EntId varchar(20),Score decimal(10,2))--统计后的数据

if @Class='1'--按细则
begin
declare @fp int,@id int,@fscnst float
delete from #s
declare c_m cursor for select distinct Entid,Fid
from @t
open c_m
while 1=1
begin
fetch c_m into @ent,@fid
if @@fetch_status<>0 break
delete from #sort
set @nCount=0

insert into #sort(score)
select IsNull(Score,0)
from @t
where Entid=@ent and Fid=@fid
order by Score
if @high>0
delete from #sort
where nid>(select max(nId) from #sort)-@high
if @Low>0
delete from #sort
where nid<(select min(nid)from #sort)+@low
select @nCount=Count(*)
from #sort
if(@nCount>0)and((@nZj-@high-@Low)>0)
select @fscnst=sum(Isnull(Score,0))/(@nZj-@high-@Low)
from #sort
else
set @fscnst=0
insert into #s(Fid,EntId,Score)
values( @fid,@ent,@fscnst)
--统计根节点数据
select @fp=FParentid from tblCfg_Tech where FXbid=@xbid and Fid=@fid
while 1=1
begin
if(@fp=-1)or(@fp is null) break
set @id=@fp
if(select count(*)from #s where Fid=@id and Entid=@ent)>0
begin
update #s
set Score=IsNull(Score,0)+@fscnst
where Fid=@id and Entid=@ent
end
else begin
insert into #s
values(@id,@ent,@fscnst)
end
select @fp=FParentid from tblCfg_Tech where FXbid=@xbid and Fid=@id
end
end
close c_m
deallocate c_m
--写企业预算
-- declare @fs float,@Flag int

declare c_y cursor for select distinct EntId
from #s
open c_y
while 1=1
begin
fetch c_y into @ent
if @@fetch_status<>0 break
select @fs=Sum(IsNull(Score,0))
from #s
where EntId=@ent

if @fs>=@ScoreLine
set @Flag=0
else
set @flag=1

update tblBid_EnterpriseYS
set FTechScore=@fs,FWasteFlag=@flag
where FXBID=@xbid and FItemID=@ent and FNodeType=2
end
Close c_y
deallocate c_y
set @v =')Insert into #t select FID,FParentID,FNodeType,FItem,FHeightScore'
set @r = ' from tblCfg_Tech where FXBID='''+@XBID+''''
end

else if @class='0'
begin--按评委处理后每个企业只有一条数据

delete from #s
declare c_e cursor for select distinct Entid
from @t
open c_e
while 1=1
begin
fetch c_e into @ent
if @@fetch_status<>0 break

delete from @grp
delete from #sort

insert into @grp
select Expert,Sum(IsNull(Score,0))
from @t
where Entid=@ent
group by Expert

insert into #sort(Score)
select Score
from @grp
order by Score
if @high>0
delete from #sort
where nid>(select max(nId) from #sort)-@high
if @Low>0
delete from #sort
where nid<(select min(nid)from #sort)+@low
select @nCount=Count(*)
from #sort
if(@nCount>0)and((@nZj-@high-@Low)>0)
begin

insert into #s(EntId,Score)
select @ent,convert(decimal(10,2),sum(Isnull(Score,0))/(@nZj-@high-@Low))
from #sort
end
else begin
insert into #s(EntId,Score)
select @ent,0
from #sort
end
end
close c_e
deallocate c_e
--写预算
update tblBid_EnterpriseYS
Set FTechScore=IsNull(Score,0)
from #s a, tblBid_EnterpriseYS b
where b.FXBID=@xbid and b.FItemID=a.entid and b.FNodeType=2
update tblBid_EnterpriseYS
set FWasteFlag=0
where FXBID=@xbid and FNodeType=2 and FTechScore>=@fs
update tblBid_EnterpriseYS
set FWasteFlag=0
where FXBID=@xbid and FNodeType=2 and FTechScore<@fs
--
set @v =')Insert into #t select 1,0,2,'''+'1'+''''+','+convert(varchar(6),convert(decimal(12,2),@fmax))
set @r = ''
end
else begin
set @v =')Insert into #t select FID,FParentID,FNodeType,FItem,FHeightScore'
set @r = ' from tblCfg_Tech where FXBID='''+@XBID+''''
end

set @Strfmt=@EntList

if @nCnt>0
begin
declare @f varchar(5),@n int,@npos int
set @n=0
while @nCnt>@n
begin
set @n=@n+1
set @tb=@tb+',F'+convert(varchar(5),@n)+' Float'
set @v=@v+',0'

set @nPos=charindex(',',@Strfmt)
if @npos=0 and len(@Strfmt)>0
begin
set @npos=len(@Strfmt)
if @Class='1'--按细则
set @set=@set+' update #t set F'+convert(varchar(5),@n) +'=IsNull(a.Score,0) from #s a where a.FId=#t.FId and a.EntID='''+SubString(@Strfmt,1,@npos)+''''
else--按评委
set @set=@set+' update #t set F'+convert(varchar(5),@n) +'=IsNull(a.Score,0) from #s a where a.EntID='''+SubString(@Strfmt,1,@npos)+''''
end
else if @npos>0
begin
if @Class='1'--按细则
set @set=@set+' update #t set F'+convert(varchar(5),@n) +'=IsNull(a.Score,0) from #s a where a.FId=#t.FId and a.EntID='''+SubString(@Strfmt,1,@npos-1)+''''
else
set @set=@set+' update #t set F'+convert(varchar(5),@n) +'=IsNull(a.Score,0) from #s a where a.EntID='''+SubString(@Strfmt,1,@npos-1)+''''
set @strfmt=SubString(@Strfmt,@nPos+1,len(@Strfmt)-@nPos)
end
if len(@set)>=6000
begin
set @strtmp=@set
set @set=''
end
end
end
else begin
set @v =')Insert into #t select FID,FParentID,FNodeType,FItem,FHeightScore'
set @r = ' from tblCfg_Tech where FXBID='''+@XBID+''''
end
--print (@tb+@v+@r+@strtmp+@set+@slt)
exec( @tb+@v+@r+@strtmp+@set+@slt)
GO

...全文