--? 建立倉庫表
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

存儲過程之銷售同時修改倉庫