sql先进先出在回退的问题 有没有sql高手知道 当数据库先进先出之后,然后在回退到初始态怎么写?
意思就是如下就是先进先出的存储过程
ALTER proc [dbo].[wsp]
@wlcode nvarchar(50),--物料编号
@cost int --出库量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare= sum(inqty)-sum(foutqty) from a074 where materialcode=@wlcode
if(@spare>=@cost)
begin
--根据入库id采用先进先出原则对货物的库存进行处理
update a074 set foutqty=
case when (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0) from a074 where materialcode=@wlcode and mxid<=a.mxid and inqty!=foutqty)>=0
then a.inqty
else
case when (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0) from a074 where materialcode=@wlcode and mxid<a.mxid and inqty!=foutqty)<0
then 0
else (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0)+a.foutqty from a074 where materialcode=@wlcode and mxid<a.mxid and inqty!=foutqty)
end
end
from a074 a where materialcode=@wlcode and inqty!=foutqty
end
else
raiserror('库存不足',16,1)
return