--SQL处理库存与销售，先进先出原则

--库存表

create table t(

id int identity(1,1),

name varchar(50),--商品名称

j int, --入库数量

c int, --出库数量

jdate datetime --入库时间

)

insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'

insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'

insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'

insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'

insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'

insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'

insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'

go

create proc wsp

@name varchar(50),

@cost int--,--销售量

--@returns int output --该货物的库存是否够(不够：-1；够：1)

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

else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate

end

end

from t a where name=@name and j!=c

end

else

raiserror('库存不足',16,1)

return

go

--测试：

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

select * from t

--删除测试环境

drop table t

drop proc wsp