..........
if (exists(select * from sysobjects where id=object_id(N'mwo3move_toal_cx')))
drop table mwo3move_toal_cx
go --要去掉这个go即可.........
if (exists(select * from sysobjects where id=object_id(N'mwo3move_toal_cx')))
drop table mwo3move_toal_cx
go --要去掉这个go即可.........
--有一下異常提示咯?幫忙看看變量定義錯啦嗎?/
伺服器: 訊息 134,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 7
The variable name '@qt_wo_ytd' has already been declared. Variable names must be unique within a query batch or stored procedure.
伺服器: 訊息 137,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 23
Must declare the variable '@qt_wo_td'.
伺服器: 訊息 134,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 29
The variable name '@no_wk' has already been declared. Variable names must be unique within a query batch or stored procedure.
伺服器: 訊息 137,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 31
Must declare the variable '@dt_wk'.
伺服器: 訊息 137,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 37
Must declare the variable '@dt_wk'.
伺服器: 訊息 137,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 41
Must declare the variable '@dt_wk'.
伺服器: 訊息 137,層級 15,狀態 1,程序 SP_ClrRate_GZBL_cx,行 46
Must declare the variable '@dt_wk'.
if (exists(select * from sysobjects where id=object_id(N'SP_ClrRate_GZBL_cx')))
drop PROCEDURE SP_ClrRate_GZBL_cx
go
CREATE PROCEDURE .[SP_ClrRate_GZBL_cx]
@Startdate varchar(12),
@Enddate varchar(12)
AS
declare @clr_rate float,@qt_wo_ytd decimal, @qt_wo_td decimal,@dt_wk varchar
----統計各站不良
if (exists(select * from sysobjects where id=object_id(N'mwo3move_toal_cx')))
drop table mwo3move_toal_cx
/*if @startdate='' and @Enddate=''
select a.dt_wk,a.wc_name,b.no_item_p,item_name,sum(b.qt_wo_toall) as qt_wo_toall, sum(QT_wip_i) as QT_wip_i,sum(b.QT_WO_CLR) as qt_wo_clr,cast(@clr_rate as float) as clr_rate,cast(@qt_wo_ytd as decimal) as qt_wo_ytd, cast(@qt_wo_td as decimal) as qt_wo_td into mwo3move_toal_cx from mwo3move_d b
left join mwo3move a on a.id_tran=b.id_tran where b.no_item_p>'' and a.optype_code='101' and a.dt_wk<>'SR010' and b.no_wc_out_name not like '委外%' group by a.dt_wk,a.wc_name,b.no_item_p,item_name
if @startdate>'' and @enddate=''
select a.dt_wk,a.wc_name,b.no_item_p,item_name,sum(b.qt_wo_toall) as qt_wo_toall, sum(QT_wip_i) as QT_wip_i,sum(b.QT_WO_CLR) as qt_wo_clr,cast(@clr_rate as float) as clr_rate,cast(@qt_wo_ytd as decimal) as qt_wo_ytd, cast(@qt_wo_td as decimal) as qt_wo_td into mwo3move_toal_cx from mwo3move_d b
left join mwo3move a on a.id_tran=b.id_tran where b.no_item_p>'' and a.optype_code='101' and b.dt_cret>=@startdate and a.dt_wk<>'SR010' and b.no_wc_out_name not like '委外%' group by a.dt_wk,a.wc_name,b.no_item_p,item_name
if @startdate='' and @enddate>''
select a.dt_wk,a.wc_name,b.no_item_p,item_name,sum(b.qt_wo_toall) as qt_wo_toall, sum(QT_wip_i) as QT_wip_i,sum(b.QT_WO_CLR) as qt_wo_clr,cast(@clr_rate as float) as clr_rate,cast(@qt_wo_ytd as decimal) as qt_wo_ytd, cast(@qt_wo_td as decimal) as qt_wo_td into mwo3move_toal_cx from mwo3move_d b
left join mwo3move a on a.id_tran=b.id_tran where b.no_item_p>'' and a.optype_code='101' and b.dt_cret<=@enddate and a.dt_wk<>'SR010' and b.no_wc_out_name not like '委外%' group by a.dt_wk,a.wc_name,b.no_item_p,item_name
*/
if @startdate>'' and @enddate>''
select a.dt_wk,a.wc_name,b.no_item_p,item_name,sum(b.qt_wo_toall) as qt_wo_toall, sum(QT_wip_i) as QT_wip_i,sum(b.QT_WO_CLR) as qt_wo_clr,cast(@clr_rate as float) as clr_rate,cast(@qt_wo_ytd as decimal) as qt_wo_ytd, cast(@qt_wo_td as decimal) as qt_wo_td into mwo3move_toal_cx from mwo3move_d b
left join mwo3move a on a.id_tran=b.id_tran where b.no_item_p>'' and a.optype_code='101' and b.dt_cret>=@startdate and b.dt_cret<=@enddate and a.dt_wk<>'SR010' and b.no_wc_out_name not like '委外%' group by a.dt_wk,a.wc_name,b.no_item_p,item_name
--使用游標找期初和期末值
declare @no_wk varchar(20),@no_item_p varchar(20)
declare mwo3move_toall_cursor cursor for
select dt_wk,no_item_p from mwo3move_toall
--declare @no_wk varchar(20),@no_item varchar(20)
open mwo3move_toall_cursor
fetch next from mwo3move_toall_cursor into @dt_wk,@no_item_p
while (@@fetch_status <> -1)
begin
declare @qichu int,@qimo int
--得到起初庫存
select @qichu=(select top 1 qt_wo_ytd from mwo3move_d where dt_wk=@dt_wk and no_item_p=@no_item_p and dt_cret=@startdate and qt_wo_ytd>0 )
--得到期末庫存
select @qimo=(select top 1 qt_wo_td from mwo3move_d where dt_wk=@dt_wk and no_item_p=@no_item_p and dt_cret=@enddate and qt_wo_ytd>0 )
--更新mwo3move_toall 的起初和期末庫存值
update mwo3move_toall set qt_wo_ytd=isnull(@qichu,0),qt_wo_td=isnull(@qimo,0) from mwo3move_toall where no_item_p=@no_item_p and dt_wk=@no_wk
fetch next from mwo3move_toall_cursor into @dt_wk,@no_item_p
end
close mwo3move_toall_cursor
deallocate mwo3move_toall_cursor
--select * from mwo3move_toal_cx
GO
----執行過程
SP_ClrRate_GZBL_cx @startdate='2005/11/01',@enddate='2005/11/10'
select * from mwo3move_toal_cx
WP020 塑鍵課 噴塗 FR02169301000#3 TY-1693-01#噴面(R) .00 4680.0000 373.00 NULL NULL NULL
WP030 塑鍵課 印刷 SP02207603A00#1 TY-2076A SP#印刷(P) 498.00 NULL NULL NULL NULL NULL
WP040 塑鍵課 鐳雕 FR01206709000#5 TY-2067-09#鐳雕(R) 9608.00 NULL 473.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR01207701000#4 TY-2077-01#鐳雕(R) 30424.00 35163.0000 576.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR01208201000#6 TY-2082-01#鐳雕(R) 4882.00 NULL 12.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR01208901000#5 TY-2089-01#鐳雕(R) 48743.00 25786.0000 1981.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR01232001000#6 TY-2320-01#鐳雕(R) .00 NULL 2653.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR01232002000#6 TY-2320-02#鐳雕(R) 964.00 546.0000 1150.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR02230401000#4 TY-2304-01#鐳雕(R) 965.00 2310.0000 157.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR03169602000#4 TY-1696-02#鐳雕(R) 174.00 NULL 218.00 NULL NULL NULL
WP040 塑鍵課 鐳雕 FR03169603000#4 TY-1696-03#鐳雕(R) 3929.00 1017.0000 132.00 NULL NULL NULL
WP050 塑鍵課 Coating FR01206209000#6 TY-2062-09#Coating(R) 402964.00 395948.0000 2342.00 NULL NULL NULL
WP050 塑鍵課 Coating FR01206701000#7 TY-2067-01#Coating(R) 27564.00 19318.0000 337.00 NULL NULL NULL
WP050 塑鍵課 Coating FR01206709000#6 TY-2067-09#Coating(R) 33116.00 19940.0000 333.00 NULL NULL NULL
WP050 塑鍵課 Coating FR01208901000#6 TY-2089-01#Coating(R) 53228.00 42100.0000 924.00 NULL NULL NULL
WP050 塑鍵課 Coating FR02230401000#5 TY-2304-01#Coating(R) 843.00 843.0000 NULL NULL NULL NULL
while (@@fetch_status =0)还有看看你是否有满足条件的更新.