if exists (select name from sysobjects where name = 'MouldUse' and type = 'p')
drop procedure moulduse
gocreate procedure MouldUse
@mouldid varchar(20)
as
declare @SulfurationTime integer
declare @prod_date datetime
declare @factsets1 numeric(8)
declare @totalTime integer
declare @totalcs integer
select * into #temp from
(select (b.SulfurationTime * a.factsets1) as SulfurationTime, a.prod_date, a.factsets1 ,0 as totalcs, 0 as totaltime from WorkRoom_Plan_TB a join Material_All_TB b
on a.pro_id = b.mat_id where mouldno1 = @mouldid
union
select (b.SulfurationTime * a.factsets1) as SulfurationTime, a.prod_date, a.factsets2, 0 as totalcs, 0 as totaltime from WorkRoom_Plan_TB a join Material_All_TB b
on a.pro_id = b.mat_id where mouldno2 = @mouldid
union
select (b.SulfurationTime * a.factsets1) as SulfurationTime, a.prod_date, a.factsets3, 0 as totalcs, 0 as totaltime from WorkRoom_Plan_TB a join Material_All_TB b
on a.pro_id = b.mat_id where mouldno3 = @mouldid)a
select * from #temp
declare t cursor for select SulfurationTime, prod_date, factsets1 from #temp
open t
fetch next from t into @SulfurationTime, @prod_date, @factsets1
while @@fetch_status = 0
begin
set @totalcs = @totalcs + @factsets1
set @totaltime = @totaltime + @SulfurationTime
-- delete #temp
-- insert into #temp1 values (@SulfurationTime, @prod_date, @factsets1, @totalcs, @totaltime)
update #temp set totalcs = @totalcs, totaltime = @totaltime where prod_date = @prod_date
fetch next from t into @SulfurationTime, @prod_date, @factsets1
end
close t
deallocate t
select * from #temp
go提示:
列名 'totalcs' 无效。
drop procedure moulduse
gocreate procedure MouldUse
@mouldid varchar(20)
as
declare @SulfurationTime integer
declare @prod_date datetime
declare @factsets1 numeric(8)
declare @totalTime integer
declare @totalcs integer
select * into #temp from
(select (b.SulfurationTime * a.factsets1) as SulfurationTime, a.prod_date, a.factsets1 ,0 as totalcs, 0 as totaltime from WorkRoom_Plan_TB a join Material_All_TB b
on a.pro_id = b.mat_id where mouldno1 = @mouldid
union
select (b.SulfurationTime * a.factsets1) as SulfurationTime, a.prod_date, a.factsets2, 0 as totalcs, 0 as totaltime from WorkRoom_Plan_TB a join Material_All_TB b
on a.pro_id = b.mat_id where mouldno2 = @mouldid
union
select (b.SulfurationTime * a.factsets1) as SulfurationTime, a.prod_date, a.factsets3, 0 as totalcs, 0 as totaltime from WorkRoom_Plan_TB a join Material_All_TB b
on a.pro_id = b.mat_id where mouldno3 = @mouldid)a
select * from #temp
declare t cursor for select SulfurationTime, prod_date, factsets1 from #temp
open t
fetch next from t into @SulfurationTime, @prod_date, @factsets1
while @@fetch_status = 0
begin
set @totalcs = @totalcs + @factsets1
set @totaltime = @totaltime + @SulfurationTime
-- delete #temp
-- insert into #temp1 values (@SulfurationTime, @prod_date, @factsets1, @totalcs, @totaltime)
update #temp set totalcs = @totalcs, totaltime = @totaltime where prod_date = @prod_date
fetch next from t into @SulfurationTime, @prod_date, @factsets1
end
close t
deallocate t
select * from #temp
go提示:
列名 'totalcs' 无效。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货