更新库存数据，先进先出存储过程

xiaoer_1982

--> 测试数据: t
If not object_id('[t]') is null
Drop table [t]
Go
create table t(id int,number numeric(5,1))
insert into t
select 1,20.0 union all
select 2,30.0 union all
select 3,40.0
go

If not object_id('[wsp]') is null
Drop proc [wsp]
Go
create proc wsp
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare int --剩余库存
select @spare=sum(number) from t
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set number=
case when (select @cost-isnull(sum(number),0) from t where id<=a.id)>=0
then 0
else
case when (select @cost-isnull(sum(number),0) from t where id<a.id)>0 then
a.number-(select @cost-isnull(sum(number),0) from t where id<a.id)
else a.number end
end
from t a
end
else
raiserror('库存不足',16,1)
return
go



exec wsp 30

select * from t
/*
id number
----------- ---------------------------------------
1 0.0
2 20.0
3 40.0
(3 行受影响)
*/


http://topic.csdn.net/u/20090806/20/09c8f9bd-493d-49e1-a662-2909757c3511.html