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 --销售量
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<a.jdate AND j!=c)<0 THEN 0
ELSE (SELECT @cost-isnull(SUM(j),0)+isnull(SUM(c),0)+a.c FROM t WHERE name=@name AND jdate<a.jdate AND j!=c)
END
END
FROM t a WHERE name=@name AND j!=c
END
ELSE
raiserror('库存不足',16,1)
RETURN
GO
--测试：
EXEC wsp @name='A',@cost=180
SELECT * FROM t
--drop table t
--drop proc wsp
//SQL/1212