SQL用先进先出存储过程求出库数量

 1 create table t( 
 2 id int identity(1,1), name varchar(50),--商品名称 
 3 j int,        --入库数量 
 4 c int,        --出库数量 
 5 jdate datetime --入库时间 
 6 ) 
 7 insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01' 
 8 insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07' 
 9 insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21' 
10 insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15' 
11 insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03' 
12 insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01' 
13 insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01' 
14 go

create proc wsp 
@name varchar(50),--商品名称 
@cost int         --销售量 
as 
--先得出该货物的库存是否够 
declare @spare float --剩余库存 
select @spare=sum(j)-sum(c) from t where name=@name 
if(@spare>=@cost)                         
   begin 
    --根据入库日期采用先进先出原则对货物的库存进行处理  
     update t set c=                                                                                                                      
     case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0 
     then a.j 
     else                        
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 
        then 0 
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) 
        end 
     end 
     from t a where name=@name and j!=c 
   end 
else 
    raiserror('库存不足',16,1)    
    return 
go

exec wsp @name='A',@cost=100 

 

转载于:https://www.cnblogs.com/jiaojuan/p/5987899.html