BEGIN TRY
--這里寫可能導(dǎo)致錯(cuò)誤的語句
END TRY
BEGIN CATCH
--當(dāng)錯(cuò)誤發(fā)生后,這里的語句將會被執(zhí)行
END CATCH
-- 其他語句

IF OBJECT_ID('spDivideTwoNumbers','P') IS NOT NULL
    DROP PROCEDURE spDivideTwoNumbers
GO
CREATE PROCEDURE spDivideTwoNumbers(
@Number1 INT, 
@Number2 INT
)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    SET @Result = @Number1 / @Number2
    PRINT '結(jié)果是: ' + CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT '第二個(gè)數(shù)字不能為0'
  END CATCH
END

exec spDivideTwoNumbers 100,1

exec spDivideTwoNumbers 100,0

ALTER PROCEDURE spDivideTwoNumbers(
@Number1 INT, 
@Number2 INT
)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    SET @Result = @Number1 / @Number2
    PRINT '結(jié)果是: ' + CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT ERROR_MESSAGE()
  END CATCH
END

IF OBJECT_ID('dbo.Product','U') IS NOT NULL
    DROP TABLE dbo.Product
IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
    DROP TABLE dbo.ProductSales
GO
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY, 
  Name VARCHAR(40), 
  Price INT,
  Quantity INT
 )
GO
INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)
GO
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO
INSERT INTO ProductSales VALUES(1, 101, 10)
INSERT INTO ProductSales VALUES(2, 102, 15)
INSERT INTO ProductSales VALUES(3, 103, 30)
INSERT INTO ProductSales VALUES(4, 104, 35)
GO

CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN

  -- 首先我們需要檢查待銷售產(chǎn)品的可用庫存
  DECLARE @StockAvailable INT
  SELECT @StockAvailable = QuantityAvailable FROM Product WHERE ProductId = @ProductId

    --如果可用庫存小于要銷售的數(shù)量,拋出錯(cuò)誤
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('可用庫存不足',16,1)
  END
  
  -- 如果可用庫存充足
  ELSE
  BEGIN
    BEGIN TRY
      -- 我們需要開啟一個(gè)事務(wù)
      BEGIN TRANSACTION

      -- 首先做減庫存操作
      UPDATE Product SET Quantity = (Quantity - @QuantityToSell) WHERE ProductID = @ProductID

      -- 計(jì)算當(dāng)前最大的產(chǎn)品銷售ID,即 MaxProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT @MaxProductSalesId = CASE 
          WHEN  MAX(ProductSalesId) IS NULL THEN 0 
          ELSE MAX(ProductSalesId) 
          END 
      FROM ProductSales

      -- 把 @MaxProductSalesId 加一, 所以我們會避免主鍵沖突 
      --(解釋下,建表的時(shí)候,沒有設(shè)置主鍵自增,所以需要人工處理自增)
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- 把銷售的產(chǎn)品數(shù)量記錄到ProductSales表中
      INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- 最后,提交事務(wù)
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      -- 如果發(fā)生了異常,回滾事務(wù)
      ROLLBACK TRANSACTION
      -- 輸出錯(cuò)誤詳情
      SELECT ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_STATE() as ErrorState,
          ERROR_SEVERITY() as ErrorSeverity,
          ERROR_LINE() as ErrorLine
    END CATCH
  End
END