事务的使用

最新推荐文章于 2023-07-02 21:19:29 发布

转载最新推荐文章于 2023-07-02 21:19:29 发布 · 85 阅读

本文介绍了一个用于处理订单状态变更的SQL存储过程，包括商品数量更新及订单状态更改。该过程支持发货和退货两种操作，并确保了数据一致性。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Pc_DeliverGoods]
@OutID INT = NULL output,
@cmd varchar(10) = null,
@CompanyOrderCode varchar(50) = null,
@IsConfirm varchar(10)=null
AS

IF @cmd = 'DeliverGoods'
   BEGIN
      BEGIN TRAN
        --更新Pdt_Price表中的订购商品的数量
       ;WITH OrderPriceIdInfo as (SELECT PriceId,Num FROM Pdt_Order WHERE CompanyOrderCode=@CompanyOrderCode)
       Update [Product]..[Pdt_Price] set Reserve = Reserve-b.Num from (select PriceId,Num from OrderPriceIdInfo) b
       WHERE [Product]..[Pdt_Price].PriceId in (select PriceId from OrderPriceIdInfo);

        --更新Pdt_Detail表中的订购商品的数量
       WITH OrderPdtIdInfo as (SELECT PdtId,sum(Num) as Num FROM Pdt_Order WHERE CompanyOrderCode=@CompanyOrderCode group by PdtId)
       Update [Product]..[Pdt_Detail] set ReserveNum = ReserveNum-b.Num from (select PdtId,Num from OrderPdtIdInfo) b
       WHERE [Product]..[Pdt_Detail].PdtId in (select PdtId from OrderPdtIdInfo);

        --更新订单状态
        UPDATE [CO_Orde] SET IsConfirm =@IsConfirm,SendDate=getdate() WHERE CompanyOrderCode=@CompanyOrderCode
      SET @OutID=0

     IF @@ERROR!=0
        BEGIN
            SET @OutID = -1
            ROLLBACK
         END
      ELSE
         BEGIN
            COMMIT
        END
    END

ELSE IF @cmd = 'BackGoods'
   BEGIN
      BEGIN TRAN
        --更新Pdt_Price表中的订购商品的数量
       ;WITH OrderPriceIdInfo as (SELECT PriceId,Num FROM Pdt_Order WHERE CompanyOrderCode=@CompanyOrderCode)
       Update [Product]..[Pdt_Price] set Reserve = Reserve+b.Num from (select PriceId,Num from OrderPriceIdInfo) b
       WHERE [Product]..[Pdt_Price].PriceId in (select PriceId from OrderPriceIdInfo);

        --更新Pdt_Detail表中的订购商品的数量
       WITH OrderPdtIdInfo as (SELECT PdtId,sum(Num) as Num FROM Pdt_Order WHERE CompanyOrderCode=@CompanyOrderCode group by PdtId)
       Update [Product]..[Pdt_Detail] set ReserveNum = ReserveNum+b.Num from (select PdtId,Num from OrderPdtIdInfo) b
       WHERE [Product]..[Pdt_Detail].PdtId in (select PdtId from OrderPdtIdInfo);

        --更新订单状态
        UPDATE [CO_Orde] SET IsConfirm =@IsConfirm WHERE CompanyOrderCode=@CompanyOrderCode
      SET @OutID=0

     IF @@ERROR!=0
        BEGIN
            SET @OutID = -1
            ROLLBACK
         END
      ELSE
         BEGIN
            COMMIT
        END
    END