我的随笔

只看楼主倒序阅读 0楼发表于: 2009-02-05

如题请高手指点附升级日志：
其中 wbs_fullcode 是什么？因该是列名吧？
@@@@@@@@@@@@@@@@@@@@@@@
CREATE procedure KB_Tur_equipment(@project_names varchar(8000))
AS

declare @epstype varchar(50)
declare @projectid varchar(30)
declare @sql varchar(8000)
if object_id('tempdb.dbo.#t') is not null
drop table #t
create table #t( seq int IDENTITY (1, 1) NOT NULL,equiparea varchar(100) null ,equipcode varchar(100),equipname VARCHAR(100),wbs_fullcode varchar(150) )
set @sql = ' alter table #t  add   '
declare cur_ cursor for select epstype,project_id from  rec_querylink where  funcname like '汽管相关设备工程量%'
open cur_
fetch cur_ into @epstype,@projectid
while @@fetch_status = 0
begin
  set @sql = @sql + ' ['+@epstype + '] float null,'

  fetch cur_ into @epstype,@projectid
end
close cur_
deallocate cur_
set @sql = substring(@sql,1,len(@sql)-1)
print (@sql)
if right(@sql,2)<>'ad'
exec (@sql)
set @sql = ' alter table #t  add  总重  float '
--exec (@sql)
insert #t(equiparea,equipcode,equipname,wbs_fullcode)
select distinct  equiparea,equipcode,equipname,wbs_fullcode from view_tur_sum_equip

declare cur_ cursor for select epstype,project_id from  rec_querylink where funcname like '汽管相关设备工程量%'
open cur_
fetch cur_ into @epstype,@projectid
while @@fetch_status = 0
begin
   set @sql = 'update  #t set ['+@epstype+']=isnull(['+@epstype+'],0)+a.num  from (select project_id, equiparea,equipcode,equipname,wbs_fullcode,sum(isnull(weight,0)) as num  '
   set @sql = @sql + '  from view_tur_sum_equip   '
   set @sql = @sql + '  where project_id='''+@projectid+'''  group by project_id, equiparea,equipcode,equipname,wbs_fullcode  ) a  '
   +'  where #t.equiparea=a.equiparea and  #t.equipcode=a.equipcode and #t.equipname=a.equipname and #t.wbs_fullcode=a.wbs_fullcode '
exec (@sql)
  fetch cur_ into @epstype,@projectid
end
close cur_
deallocate cur_
select * from #t
错误信息:
列名 'wbs_fullcode' 无效。

升级失败，还原老版本的数据库成功!2009-1-4 18:59:05