--Cal QTY_ON_HAND QTY_ON_HAND_COST
update maitem set QTY_ON_HAND=isnull(A.QTY_ON_HAND,0)-isnull(B.qty,0),QTY_ON_HAND_COST=isnull(A.QTY_ON_HAND_COST,0)-( isnull(B.qty,0)*isnull(A.average_cost,0) )
from maitem A inner join
(select E.ref_no,E.item_no,E.loc,sum(E.qty) as qty
from inmovd E
where E.ref_no=@REF_NO
group by E.ref_no,E.item_no,E.loc
) B
on A.item_no=B.item_no --amend 20120704 and A.loc=B.loc and B.ref_no=@REF_NO
这是一段存储过程更新数据库的代码,现在有同一张单号的10条记录,为什么有时候却UPDATE了9条,但是其中1条UPDATE不成功,再执行一次这个存储过程又可以UPDATE到10条记录。我使用的是SQL SERVER EXPRESS 2005 (备注:REF_NO 为订单单号)
update maitem set QTY_ON_HAND=isnull(A.QTY_ON_HAND,0)-isnull(B.qty,0),QTY_ON_HAND_COST=isnull(A.QTY_ON_HAND_COST,0)-( isnull(B.qty,0)*isnull(A.average_cost,0) )
from maitem A inner join
(select E.ref_no,E.item_no,E.loc,sum(E.qty) as qty
from inmovd E
where E.ref_no=@REF_NO
group by E.ref_no,E.item_no,E.loc
) B
on A.item_no=B.item_no --amend 20120704 and A.loc=B.loc and B.ref_no=@REF_NO
这是一段存储过程更新数据库的代码,现在有同一张单号的10条记录,为什么有时候却UPDATE了9条,但是其中1条UPDATE不成功,再执行一次这个存储过程又可以UPDATE到10条记录。我使用的是SQL SERVER EXPRESS 2005 (备注:REF_NO 为订单单号)
不影响结果的。update A set QTY_ON_HAND=isnull(A.QTY_ON_HAND,0)-isnull(B.qty,0),QTY_ON_HAND_COST=isnull(A.QTY_ON_HAND_COST,0)-( isnull(B.qty,0)*isnull(A.average_cost,0) )
from maitem A inner join
(select E.ref_no,E.item_no,E.loc,sum(E.qty) as qty
from inmovd E
where E.ref_no=@REF_NO
group by E.ref_no,E.item_no,E.loc
) B
on A.item_no=B.item_no --amend 20120704 and A.loc=B.loc and B.ref_no=@REF_NO
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO--exec spPost_INF002 'INF001'ALTER PROCEDURE [dbo].[spPost_INF002]
@REF_NO nvarchar(16),
@ReturnErrorNum nvarchar(10) OUTPUT,
@ReturnMessage varchar(4000) OUTPUT
ASdeclare @Err_Message nvarchar(100)Set @Err_Message = ''
Set @ReturnErrorNum=''
Set @ReturnMessage=''Begin Tran T1
--check Issue Qty > Qty On Hand
if(
/*
select count(*)
from maitem A,
(select ref_no,item_no,loc,sum(qty) as qty
from inmovd
where ref_no=@REF_NO
group by ref_no,item_no,loc
) B
where A.item_no=B.item_no and A.loc=B.loc and A.qty_on_hand<B.qty and B.ref_no=@REF_NO
*/ select count(*)
from
(select ref_no,item_no,loc,sum(qty) as issue_qty
from inmovd
where ref_no=@REF_NO
group by ref_no,item_no,loc
) B left outer join maitem A
on B.item_no=A.item_no and B.loc=A.loc
where isnull(B.issue_qty,0)>isnull(A.qty_on_hand,0) and B.ref_no=@REF_NO)>0
begin
select B.item_no,B.issue_qty,A.qty_on_hand
into #tmpLessItem
from
(select ref_no,item_no,loc,sum(qty) as issue_qty
from inmovd
where ref_no=@REF_NO
group by ref_no,item_no,loc
) B left outer join maitem A
on B.item_no=A.item_no and B.loc=A.loc
where isnull(B.issue_qty,0)>isnull(A.qty_on_hand,0) and B.ref_no=@REF_NO
order by B.item_no declare @Col_List varchar(4000) select @Col_List=COALESCE(@Col_List+'EnterLine','')+N'ItemNo:'+item_no+N' IssueQty:'+convert(nvarchar(30),isnull(issue_qty,0))+N' QtyOnHand:'+convert(nvarchar(30),isnull(qty_on_hand,0))
from #tmpLessItem set @ReturnMessage=@Col_List
set @ReturnErrorNum='E01'
Set @Err_Message = '|Error detected!' If @@ERROR <> 0 Set @Err_Message = '|Error detected!'endelse
begin
/*
delete inbaln
from inbaln A,
(select ref_no,item_no,loc,lot_no,sum(qty) as qty
from inmovd
where ref_no=@REF_NO
group by ref_no,item_no,loc,lot_no
) B
where A.item_no=B.item_no and A.loc=B.loc and A.lot_no=B.lot_no and A.qty_on_hand=B.qty and B.ref_no=@REF_NOIf @@ERROR <> 0 Set @Err_Message = '|Error detected!'
*/
--Cal QTY_ON_HAND QTY_ON_HAND_COST
update maitem set QTY_ON_HAND=isnull(A.QTY_ON_HAND,0)-isnull(B.qty,0),QTY_ON_HAND_COST=isnull(A.QTY_ON_HAND_COST,0)-( isnull(B.qty,0)*isnull(A.average_cost,0) )
from maitem A inner join
(select E.ref_no,E.item_no,E.loc,sum(E.qty) as qty
from inmovd E
where E.ref_no=@REF_NO
group by E.ref_no,E.item_no,E.loc
) B
on A.item_no=B.item_no --amend 20120704 and A.loc=B.loc and B.ref_no=@REF_NO If @@ERROR <> 0 Set @Err_Message = '|Error detected!'--update post average_cost to inmovd
update inmovd set POST_AVERAGE_COST=isnull(B.average_cost,0)
from inmovd A, maitem B
where A.item_no=B.item_no and A.loc=B.loc and A.ref_no=@REF_NO--Cal average_cost
update maitem set average_cost=isnull(A.QTY_ON_HAND_COST,0)/isnull(QTY_ON_HAND,0)
from maitem A,inmovd B
where A.item_no=B.item_no and A.loc=B.loc and B.ref_no=@REF_NO and isnull(A.QTY_ON_HAND,0)>0update maitem set average_cost=0
from maitem A,inmovd B
where A.item_no=B.item_no and A.loc=B.loc and B.ref_no=@REF_NO and isnull(A.QTY_ON_HAND,0)=0If @@ERROR <> 0 Set @Err_Message = '|Error detected!'update maitem set last_out_date=C.move_date,last_out_price=B.unit_cost
from maitem A, inmovd B, inmovh C
where A.item_no=B.item_no and A.loc=B.loc and B.ref_no=C.ref_no and B.ref_no=@REF_NOIf @@ERROR <> 0 Set @Err_Message = '|Error detected!'update inmovh set posted=1,posted_date=getdate(),posted_by='' where ref_no=@REF_NOIf @@ERROR <> 0 Set @Err_Message = '|Error detected!'declare @CUMULATE_MARK_RULE decimal(18,0)select @CUMULATE_MARK_RULE=(select isnull(CUMULATE_MARK_RULE,0) from adparm)update macust set cumulate_amt=isnull(A.cumulate_amt,0)+isnull(B.total_cost,0)
from macust A,inmovh B
where A.customer_name=B.customer and B.ref_no=@REF_NOupdate macust set cumulate_=round(isnull(A.cumulate_amt,0)/@CUMULATE_MARK_RULE,0,1)
from macust A,inmovh B
where A.customer_name=B.customer and B.ref_no=@REF_NO and @CUMULATE_MARK_RULE>0If @@ERROR <> 0 Set @Err_Message = '|Error detected!'end
If @Err_Message = '' Begin
Commit Tran T1
Set @Err_Message = 'successfully'
End
Else Begin
Rollback Tran T1
End