stock_backing.txt

< 飞鸿商品>>零售是基于VB+SQL2000开的商品零售管理系统. 开发的很好.可以一看

CREATE PROCEDURE  stock_backing  @ruku_id varchar(30), @id varchar(30), 
	@unit varchar(20), @inprice smallmoney, @num smallint, @gongying varchar(30),
	@date varchar(20), @time varchar(20), @username varchar(20)
  AS
DECLARE @nums smallint

SELECT @nums = @num * (SELECT switch FROM yaopin_unit WHERE id=@id and unit=@unit)

UPDATE yaopin_num SET num=num-@nums WHERE [id]=@id

UPDATE ruku_list SET num=num-@num WHERE [id]=@ruku_id AND yaopin_id=@id

UPDATE ruku_index SET [sum]=[sum] - @inprice * @num WHERE [id]=@ruku_id

UPDATE yaopin_cost SET cost=( SELECT SUM((a.inprice / b.switch) * (a.num * b.switch)) / SUM(a.num * b.switch) 
   FROM ruku_list a INNER JOIN  yaopin_unit b ON a.yaopin_id = b.id AND a.unit = b.unit WHERE (a.yaopin_id = @id))

INSERT INTO stock_back VALUES (@ruku_id, @id, @unit, @inprice, @num, @gongying, @date, @time, @username)
GO