如题请高手指点 附升级日志:
M A} = 其中 wbs_fullcode 是什么?因该是列名吧?
yX$I<L<Suz @@@@@@@@@@@@@@@@@@@@@@@
C16MzrB}(N CREATE procedure KB_Tur_equipment(@project_names varchar(8000))
;E(gl$c: AS
bRJYw6oA< { r`l declare @epstype varchar(50)
x6* {@J&5* declare @projectid varchar(30)
Um)0jT declare @sql varchar(8000)
g[@K
d if object_id('tempdb.dbo.#t') is not null
/YR*KxIx drop table #t
S6h=}
V) create table #t( seq int IDENTITY (1, 1) NOT NULL,equiparea varchar(100) null ,equipcode varchar(100),equipname VARCHAR(100),wbs_fullcode varchar(150) )
7e/Uc!&* set @sql = ' alter table #t add '
zP
e4WE| declare cur_ cursor for select epstype,project_id from rec_querylink where funcname like '汽管相关设备工程量%'
hc5M)0d open cur_
lInq= fetch cur_ into @epstype,@projectid
-i V&-oP while @@fetch_status = 0
v/c8P\ begin
V=VL@= set @sql = @sql + ' ['+@epstype + '] float null,'
sT[)r]`T C.C)&&|X fetch cur_ into @epstype,@projectid
WiH%URFB end
xH;qJRHa close cur_
X, <l deallocate cur_
'jZ2^ set @sql = substring(@sql,1,len(@sql)-1)
}pbyC print (@sql)
Mlb=,l if right(@sql,2)<>'ad'
p/:)Z_ exec (@sql)
i6-q%%]6 set @sql = ' alter table #t add 总重 float '
&32qv`
V_ --exec (@sql)
b,Ed}Ir insert #t(equiparea,equipcode,equipname,wbs_fullcode)
F@kOj*5,[ select distinct equiparea,equipcode,equipname,wbs_fullcode from view_tur_sum_equip
ge`)sB, 9r
fR declare cur_ cursor for select epstype,project_id from rec_querylink where funcname like '汽管相关设备工程量%'
V5i_\A open cur_
fPiq
fetch cur_ into @epstype,@projectid
i)1E[jc{p! while @@fetch_status = 0
n>Y3hY begin
y%|nE(( 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 '
2}'qu) set @sql = @sql + ' from view_tur_sum_equip '
~q?IG5s*Z set @sql = @sql + ' where
project_id='''+@projectid+''' group by project_id, equiparea,equipcode,equipname,wbs_fullcode ) a '
$hPAp} +' where #t.equiparea=a.equiparea and #t.equipcode=a.equipcode and #t.equipname=a.equipname and #t.wbs_fullcode=a.wbs_fullcode '
oB-&ma[ZS exec (@sql)
q+x4Od3 fetch cur_ into @epstype,@projectid
MnO,Cd6{%d end
82~UI'f \ close cur_
Y6`9:97 deallocate cur_
^v}Z5,aN select * from #t
L4zSro:Si 错误信息:
\_!FOUPz( 列名 'wbs_fullcode' 无效。
C:hfI;*7 "}\2zub9 升级失败,还原老版本的数据库成功!2009-1-4 18:59:05
9HLn_|yU