更新库存数据,先进先出存储过程 收藏 --> 测试数据: tIf 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 goIf 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 goexec wsp 30select * from t/*id number ----------- --------------------------------------- 1 0.0 2 20.0 3 40.0(3 行受影响)*/
--> 测试数据: tIf 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
goIf 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
goexec wsp 30select * from t/*id number
----------- ---------------------------------------
1 0.0
2 20.0
3 40.0(3 行受影响)*/