這個procedure哪兒出了問題? |
尚未結案
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
我的存儲過程是用入庫明細(storein)來更新庫存(store),並把入庫單(storeinno)中的mark設為1(當mark為0時表示此單未入帳).可是執行此過程后,只有庫存數得到了更新庫存數,不知道是哪里問題,請各位先進指教!謝謝.
附:procedure及表結構
create procedure sp_store as
begin
declare @mater_no varchar(12),
@price real,
@quantity real,
@rate real,
@in_no varchar
declare cur_storein scroll cursor
for select a.mater_no,a.price,a.quantity,a.rate,b.in_no from storein a inner join storeinno b on a.in_no=b.in_no where b.mark=0
open cur_storein
fetch next from cur_storein into @mater_no,@price,@quantity,@rate,@in_no
while (@@fetch_status=0)
begin
if (select count(*) from store where mater_no=@mater_no)=0
insert store (mater_no,price,quantity,rate) values (@mater_no,@price,@quantity,@rate)
else
update store set price=((price*quantity @price*@quantity)/(quantity @quantity)),quantity=quantity @quantity,rate=@rate
from store where mater_no = @mater_no
-- update storein set mark=1 where mater_no=@mater_no
update storeinno set mark=1 where in_no=@in_no
fetch next from cur_storein into @mater_no,@price,@quantity,@rate,@in_no
end
close cur_storein
deallocate cur_storein
return
end
go
create table store --庫存表
(mater_no varchar(12) constraint storePK primary key
constraint storematerFk foreign key references mater(mater_no) not null,
quantity real default 0 not null,
price real default 0 not null)
go create table storeinno --入庫單表
(in_no varchar (10) constraint innoFK primary key not null,
recieve varchar (8) ,
mark int default 0,
note varchar(50),
in_date datetime not null,
po_no varchar(12),
sn_no int IDENTITY (10000, 1),
pro_no varchar(8) not null)
go create table storein --入庫明細表
(in_no varchar(8) constraint PK_storein primary key not null,
pro_no varchar(8) not null,
price real not null,
mater_no varchar(12) not null,
in_date datetime not null,
recieve varchar(8),
quantity real,
rate real default (0),
type varchar(10),
note varchar(50) )
)
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |