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

--> 测试数据: 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