create table inDetail(AutoRkID varchar(20),ProductID varchar(20),inQty int,nowQty int,storageID int,inPrice dec(10,2),inDate datetime)
insert inDetail select 'RK20070131-9','ddj001',50,50,1,200.50,'2007-01-31 16:18:34.187'
union all select 'RK20070131-9','zhuanz001',100,100,1,20.36,'2007-01-31 16:18:34.187'
union all select 'RK20070131-10','ddj001',50,50,2,200.59,'2007-01-31 16:20:49.123'
union all select 'RK20070131-10','huotao001',200,200,2,14.00,'2007-01-31 16:20:49.123'
union all select 'RK20070131-11','ddj001',80,80,1,200.80,'2007-01-31 16:25:33.607'
union all select 'RK20070131-12','ddj001',200,200,1,200.86,'2007-01-31 16:25:53.403'
怎么实现存储过程,对按某一仓库号的商品的先进先出

解决方案 »

  1.   

    不好意思,楼兄,不太理解而已,如过按参数@storageID表示出库号,该怎么修改,谢谢
      

  2.   

    我寫的那個就是按照 storageID =1然後先進先出,不夠再出storageID=2,依次類推
      

  3.   

    Create Table inDetail(AutoRkID Varchar(20),ProductID Varchar(20),inQty Int,nowQty Int,storageID Int,inPrice Dec(10,2),inDate Datetime)
    Insert inDetail Select 'RK20070131-9','ddj001',50,50,1,200.50,'2007-01-31 16:18:34.187'
    Union All Select 'RK20070131-9','zhuanz001',100,100,1,20.36,'2007-01-31 16:18:34.187'
    Union All Select 'RK20070131-10','ddj001',50,50,2,200.59,'2007-01-31 16:20:49.123'
    Union All Select 'RK20070131-10','huotao001',200,200,2,14.00,'2007-01-31 16:20:49.123'
    Union All Select 'RK20070131-11','ddj001',80,80,1,200.80,'2007-01-31 16:25:33.607'
    Union All Select 'RK20070131-12','ddj001',200,200,1,200.86,'2007-01-31 16:25:53.403'
    GO
    Create Procedure SP_UpdatenowQty(@out Int,@productID Varchar(30), @storageID Int)
    As
    Begin
    Update 
    T1
    Set 
    nowQty = (Case When T1.inQty + T3.SUMinQty < @out Then 0 
    Else T1.inQty + T3.SUMinQty - @out End)
    From 
    inDetail T1
    Inner Join 
    (Select *, IsNull((Select SUM(inQty) From inDetail Where ProductID = T2.ProductID And storageID = T2.storageID And inDate <T2.inDate ), 0) As SUMinQty From inDetail T2) T3
    On T1.ProductID = T3.ProductID And T1.storageID = T3.storageID And T1.AutoRkID = T3.AutoRkID
    Where T3.SUMinQty - @out <= 0 And T1.ProductID = @productID And T1.storageID = @storageID
    End
    GO
    EXEC SP_UpdatenowQty 140, 'ddj001', 1Select * From inDetail
    GO
    Drop Table inDetail
    Drop Procedure SP_UpdatenowQty
    /*
    AutoRkID ProductID inQty nowQty storageID inPrice inDate
    RK20070131-9 ddj001 50 0 1 200.50 2007-01-31 16:18:34.187
    RK20070131-9 zhuanz001 100 100 1 20.36 2007-01-31 16:18:34.187
    RK20070131-10 ddj001 50 50 2 200.59 2007-01-31 16:20:49.123
    RK20070131-10 huotao001 200 200 2 14.00 2007-01-31 16:20:49.123
    RK20070131-11 ddj001 80 0 1 200.80 2007-01-31 16:25:33.607
    RK20070131-12 ddj001 200 190 1 200.86 2007-01-31 16:25:53.403
    */
      

  4.   

    测试完毕后给分,谢谢各位,特别是playwarcraft(时间就像乳沟,挤挤还是有的)仁兄的关注