[转]存储过程之销售同时修改仓库

转自：CoderDream

--  建立仓库表
IF(EXISTS(SELECT name FROM SYSOBJECTS WHERE NAME='仓库' AND TYPE='U'))
 DROP TABLE 仓库
GO

CREATE TABLE 仓库
(
 玩具号 NVARCHAR(20) PRIMARY KEY,
 库存 INT,
 平均单价 FLOAT(8)
)
GO

-- 向仓库表中添加数据
INSERT 仓库 VALUES('12-1',100,50)
INSERT 仓库 VALUES('12-2',80,80)
INSERT 仓库 VALUES('12-3',60,100)
GO

-- 创建销售表
IF(EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='销售' AND TYPE='U'))
 DROP TABLE 销售
GO

CREATE TABLE 销售
( 
 SqlID INT IDENTITY(1,1) PRIMARY KEY,
 玩具号 NVARCHAR(20),
 数量 INT,
 销售单价 FLOAT(8)
)
GO

-- 创建存储过程
IF(EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='SP_INSERTSALE' AND TYPE='P'))
 DROP PROC SP_INSERTSALE
GO
CREATE PROC SP_INSERTSALE
 @toyID nvarchar(20),
 @toyquantity int,
 @toyprice float(8)
AS
DECLARE @tempquantity int --临时变量，用于保存库存 
DECLARE @tempprice float(8) --临时变量，用于保存原始平均单价
DECLARE @tempSum float(8) --临时变量，用于保存原始总成本
DECLARE @insError int  --此变量用于保存插入时返回的@@ERROR值
DECLARE @upError int  --此变量用于保存更新时返回的@@ERROR值
BEGIN
 IF(EXISTS(SELECT * FROM 仓库 WHERE 玩具号=@toyid))
  BEGIN 
   SELECT @tempprice=平均单价 FROM 仓库 WHERE 玩具号=@Toyid
   SELECT @tempquantity=库存 FROM 仓库 WHERE 玩具号=@Toyid
   IF @tempquantity>=@toyquantity
    BEGIN
     BEGIN TRAN
     INSERT 销售(玩具号,数量,销售单价) VALUES(@toyid,@toyquantity,@toyprice)
     SELECT @InsError=@@ERROR
     SELECT @tempSum=@tempquantity*@tempprice-@toyquantity*@toyprice
     SELECT @tempprice=@tempSum/(@tempquantity-@toyquantity) FROM 销售 WHERE 玩具号=@toyid
     UPDATE 仓库 SET 库存=@tempquantity-@toyquantity,平均单价=@tempprice WHERE 玩具号=@toyid
     SELECT @upError=@@Error
     IF @InsError=0 AND @upError=0
      BEGIN
       COMMIT TRAN
       PRINT '操作成功！'  
      END
     ELSE
      BEGIN
       ROLLBACK TRAN
       PRINT '操作失败！' 
      END 
     
    END
   ELSE
    PRINT '数量不够！'
  END
 ELSE
   PRINT '没有这样的货！'
END
GO

EXEC SP_INSERTSALE '12-3',120,20
GO
-- 数量不够

EXEC SP_INSERTSALE '12-4',20,20
GO
-- 没有这样的货

EXEC SP_INSERTSALE '12-1',20,55
GO

-- （所影响的行数为 1 行）
-- 
-- 
-- （所影响的行数为 1 行）
-- 
-- 操作成功
SELECT * FROM 仓库
GO
SELECT * FROM 销售
GO