在SQL Server中使用 Try Catch 处理异常的示例详解

BEGIN TRY
--这里写可能导致错误的语句
END TRY
BEGIN CATCH
--当错误发生后,这里的语句将会被执行
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 '结果是: ' + CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT '第二个数字不能为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 '结果是: ' + 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

  -- 首先我们需要检查待销售产品的可用库存
  DECLARE @StockAvailable INT
  SELECT @StockAvailable = QuantityAvailable FROM Product WHERE ProductId = @ProductId

    --如果可用库存小于要销售的数量,抛出错误
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('可用库存不足',16,1)
  END
  
  -- 如果可用库存充足
  ELSE
  BEGIN
    BEGIN TRY
      -- 我们需要开启一个事务
      BEGIN TRANSACTioN

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

      -- 计算当前最大的产品销售ID,即 MaxProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT @MaxProductSalesId = CASE 
          WHEN  MAX(ProductSalesId) IS NULL THEN 0 
          ELSE MAX(ProductSalesId) 
          END 
      FROM ProductSales

      -- 把 @MaxProductSalesId 加一, 所以我们会避免主键冲突 
      --(解释下,建表的时候,没有设置主键自增,所以需要人工处理自增)
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- 把销售的产品数量记录到ProductSales表中
      INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- 最后,提交事务
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      -- 如果发生了异常,回滚事务
      ROLLBACK TRANSACTION
      -- 输出错误详情
      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