sql - 第二个插入语句不插入数据

Question

我的 SQL 查询中有两个插入语句。但是它仅插入第一条语句的数据。它不会为第二个插入语句插入数据。

GO

/****** Object:  StoredProcedure [dbo].[prAddUpdateOrderDetails]    Script Date: 4/10/2019 12:49:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Name                :   [prAddUpdateOrderDetails]
Parameters          :       
Descripton          :   This sp is to add and update Orders
Author              :   Saion Corp.
Sample Exec         :   DECLARE @p1 INT
                        DECLARE @p2 [dbo].[udtOrderDetails]
                            INSERT INTO @p2 VALUES(0,0,7,0,2,1,200.00,200.00,200.00,20.00,220.00,240.00,12,'01/01/2000','01/01/2000',200.00,0)
                        DECLARE @p3 [dbo].[udtOrderProductAttributes]
                            INSERT INTO @p3 VALUES(0,0,7,5,1,'Sites','5 Users',1,1,10,1,0,0,10)
                        DECLARE @p4 [dbo].[udtOrderPromotions]
                            INSERT INTO @p4 VALUES(0,0,7,5,1,'Sites','5 Users',1,1,10,1,0,0,10)
                        DECLARE @p5 [dbo].[udtOrderDetailPromotions]
                            INSERT INTO @p5 VALUES(0,0,7,5,1,'Sites','5 Users',1,1,10,1,0,0,10)
                        EXEC [prAddUpdateOrderDetails]  @OrderID = 0                                                        
                                                        ,@SegmentId = 2
                                                        ,@OrganizationId INT
                                                        ,@CreatedBy = 1
                                                        ,@OrderNumber = ''
                                                        ,@Amount = 24.0
                                                        ,@AMB = true
                                                        ,@TotalAmount = 36.00
                                                        ,@AdditionalSalesReps = ''
                                                        ,@AdditionalNotes = ''
                                                        ,@PromotionAmt =45.00
                                                        ,@Subscription = 'JSON'
                                                        ,@OrderPhaseId = 1
                                                        ,@OrderDetails = @p2
                                                        ,@OrderProductAttributes = @p3
                                                        ,@OrderPromotions = @p4
                                                        ,@OrderDetailPromotions = @p5
                                                        ,@NewOrderId = @p1 OUTPUT
                                                    SELECT @p1

Modified History    :
*/
ALTER PROCEDURE [dbo].[prAddUpdateOrderDetails] @OrderID INT
    ,@BillTo VARCHAR(20)
    ,@ShipTo VARCHAR(20)
    ,@SegmentId INT
    ,@OrganizationId INT
    ,@CreatedBy INT
    ,@OrderNumber NVARCHAR(50)
    ,@CustomerName NVARCHAR(255)
    ,@Email NVARCHAR(255)
    ,@ContactNumber NVARCHAR(30)
    ,@Amount DECIMAL(18, 2)
    ,@AMB BIT
    ,@TotalAmount DECIMAL(18, 2)
    ,@AdditionalSalesReps NVARCHAR(MAX)
    ,@AdditionalNotes NVARCHAR(50)
    ,@PromotionAmt DECIMAL(18, 2)
    ,@Subscription NVARCHAR(MAX)
    ,@OrderPhaseId INT
    ,@OrderDetails udtOrderDetails READONLY
    ,@OrderProductAttributes udtOrderProductAttributes READONLY
    ,@OrderPromotions udtOrderPromotions READONLY
    ,@OrderDetailPromotions udtOrderDetailPromotions READONLY
    ,@NewOrderId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @ErrMsg NVARCHAR(4000)
DECLARE @ErrSeverity INT
DECLARE @Quantity INT
DECLARE @ProductId INT
DECLARE @ParentProductId INT
DECLARE @WeightedAmount DECIMAL(18, 2)
DECLARE @AttributeId INT
DECLARE @AttributeName NVARCHAR(MAX)
DECLARE @CustomerEntersQty BIT
DECLARE @AttributeQuantity INT
DECLARE @TotalRecords INT
DECLARE @CurrentRow INT
DECLARE @TempOrderDetails TABLE (
    OrderId INT
    ,OrderDetailId INT
    ,ProductID INT
    )

BEGIN TRANSACTION ShopCT_TRAN;

BEGIN TRY
    -- Insert into Orders table
    IF NOT EXISTS (
            SELECT 1
            FROM [dbo].[Orders]
            WHERE OrderId = @OrderID
            )
    BEGIN
        INSERT INTO [dbo].[Orders] (
            [BillTo]
            ,[ShipTo]
            ,[SegmentId]
            ,[OrganizationId]
            ,[CreatedBy]
            ,[OrderNumber]
            ,[CustomerName]
            ,[Email]
            ,[ContactNumber]
            ,[Amount]
            ,[TotalAmount]
            ,[AdditionalSalesReps]
            ,[AdditionalNotes]
            --,[OrderDate]
            ,[PromotionAmt]
            ,[Subscription]
            ,[OrderPhaseId]
            ,[AMB]
            )
        VALUES (
            @BillTo
            ,@ShipTo
            ,@SegmentId
            ,@OrganizationId
            ,@CreatedBy
            ,@OrderNumber
            ,@CustomerName
            ,@Email
            ,@ContactNumber
            ,@Amount
            ,@TotalAmount
            ,@AdditionalSalesReps
            ,@AdditionalNotes
            --,GETDATE()
            ,@PromotionAmt
            ,@Subscription
            ,@OrderPhaseId
            ,@AMB
            );

        SET @OrderId = CONVERT(INT, SCOPE_IDENTITY());

        DECLARE @OPhaseId INT = (
                SELECT OrderPhaseId
                FROM OrderPhase
                WHERE OrderPhase = 'Shopping Carts'
                );

        -- Order Processings entry when order created or shopping cart
        INSERT INTO OrderProcessings (
            [OrderId]
            ,[OrderPhase_Id]
            ,[CreatedBy]
            )
        VALUES (
            @OrderID
            ,@OPhaseId
            ,@CreatedBy
            )

        SELECT @NewOrderId = @OrderId;
    END
    ELSE
    BEGIN
        IF (
                @OrderNumber IS NOT NULL
                AND @OrderNumber <> ''
                )
        BEGIN
            UPDATE [dbo].[Orders]
            SET OrderDate = GETDATE()
            WHERE OrderId = @OrderID
                AND OrderDate IS NULL
        END

        UPDATE [dbo].[Orders]
        SET [CreatedBy] = @CreatedBy
            ,[OrderNumber] = @OrderNumber
            ,[Amount] = @Amount
            ,[TotalAmount] = @TotalAmount
            ,[AdditionalSalesReps] = @AdditionalSalesReps
            ,[AdditionalNotes] = @AdditionalNotes
            ,[PromotionAmt] = @PromotionAmt
            ,[Subscription] = @Subscription
            ,[OrderPhaseId] = @OrderPhaseId
            ,[AMB] = @AMB
            ,[UpdatedOn] = GETDATE()
        WHERE OrderId = @OrderId
            AND BillTo = @BillTo
            AND ShipTo = @ShipTo;

        SELECT @NewOrderId = @OrderId;
    END

    --Deleting from OrderPromotion
    DELETE
    FROM [dbo].[OrderPromotions]
    WHERE [OrderId] = @OrderID;-- Removing exist Order Promotion for current order  

    --Deleting from OrderDetailsPromotion                   
    DELETE
    FROM OrderDetailPromotions
    WHERE OrderDetailId IN (
            SELECT OrderDetailId
            FROM @OrderDetails
            );-- Removing exist Order Promotion for current order
        --Deleting from OrderProductAttributes

    DELETE
    FROM [dbo].OrderProductAttributes
    WHERE OrderDetailId IN (
            SELECT OrderDetailId
            FROM @OrderDetails
            WHERE UpdateType = 2
            )
        AND OrderProductAttributes.OrderId = @OrderID;

    --Deleting from OrderProcessings
    DELETE
    FROM [dbo].[OrderProcessings]
    WHERE [OrderId] = @OrderID;

    --Deleting from OrderDetails 
    DELETE
    FROM [dbo].OrderDetails
    WHERE Id IN (
            SELECT OrderDetailId
            FROM @OrderDetails
            WHERE UpdateType = 2
            )
        AND OrderDetails.OrderId = @OrderID;

    --Insert Into OrderDetails
    INSERT INTO [dbo].[OrderDetails] (
        [OrderId]
        ,[ProductId]
        ,[ParentProductId]
        ,[SegmentId]
        ,[Quantity]
        ,[Price]
        ,[SubscriptionAttributePrice]
        ,[OneTimeAttributePrice]
        ,[PromotionSubscriptionAttributePrice]
        ,[PromotionOneTimeAttributePrice]
        ,[TotalPrice]
        ,[SubscriptionTerm]
        ,[SubscriptionStartDate]
        ,[SubscriptionEndDate]
        ,[SubscriptionTotal]
        ,[Subscription]
        ,[CreatedBy]
        )
    SELECT @OrderID
        ,[ProductId]
        ,[ParentProductId]
        ,[SegmentId]
        ,[Quantity]
        ,[Price]
        ,[SubscriptionAttributePrice]
        ,[OneTimeAttributePrice]
        ,[PromotionSubscriptionAttributePrice]
        ,[PromotionOneTimeAttributePrice]
        ,[TotalPrice]
        ,[SubscriptionTerm]
        ,[SubscriptionStartDate]
        ,[SubscriptionEndDate]
        ,[SubscriptionTotal]
        ,[Subscription]
        ,@CreatedBy
    FROM @OrderDetails
    WHERE OrderDetailId = 0
        AND UpdateType = 0;

    --Inserting New Order Details ID in @TempOrderDetails
    INSERT INTO @TempOrderDetails (
        OrderId
        ,OrderDetailId
        ,ProductID
        )
    SELECT A.OrderId
        ,A.ID
        ,A.ProductId
    FROM OrderDetails A
    INNER JOIN @OrderDetails B ON A.ProductId = B.ProductID
        AND A.OrderId = @OrderId
    WHERE B.OrderDetailId = 0
        AND B.UpdateType = 0;

    --Updating OrderDetails
    UPDATE [dbo].[OrderDetails]
    SET [OrderId] = A.OrderId
        ,[ProductId] = A.ProductId
        ,[ParentProductId] = A.ParentProductId
        ,[SegmentId] = A.SegmentId
        ,[Quantity] = A.Quantity
        ,[Price] = A.Price
        ,[SubscriptionAttributePrice] = A.[SubscriptionAttributePrice]
        ,[OneTimeAttributePrice] = A.[OneTimeAttributePrice]
        ,[PromotionSubscriptionAttributePrice] = A.[PromotionSubscriptionAttributePrice]
        ,[PromotionOneTimeAttributePrice] = A.[PromotionOneTimeAttributePrice]
        ,[TotalPrice] = A.TotalPrice
        ,[SubscriptionTerm] = A.SubscriptionTerm
        ,[SubscriptionStartDate] = A.SubscriptionStartDate
        ,[SubscriptionEndDate] = A.SubscriptionEndDate
        ,[SubscriptionTotal] = A.SubscriptionTotal
        ,[Subscription] = A.Subscription
        ,[UpdatedOn] = GETDATE()
    FROM @OrderDetails A
    INNER JOIN OrderDetails B ON A.OrderDetailId = B.Id
    WHERE B.OrderId = @OrderID
        AND A.UpdateType = 1;

    --Insert Into [dbo].[OrderProductAttributes]
    INSERT INTO [dbo].[OrderProductAttributes] (
        [OrderId]
        ,[OrderDetailId]
        ,[ProductId]
        ,[AttributeId]
        ,[Product_ProductAttribute_MappingId]
        ,[ProductAttributeValueId]
        ,[AttributeType]
        ,[AttributeName]
        ,[Name]
        ,[IsSubscription]
        ,[PriceAdjustment]
        ,[Quantity]
        ,[CustomerEntersQty]
        ,[TotalPriceAdjustment]
        ,CreatedBy
        )
    SELECT B.OrderId
        ,B.OrderDetailId
        ,A.ProductId
        ,A.AttributeId
        ,A.Product_ProductAttribute_MappingId
        ,A.ProductAttributeValueId
        ,A.AttributeType
        ,A.AttributeName
        ,A.Name
        ,A.IsSubscription
        ,A.PriceAdjustment
        ,A.Quantity
        ,A.CustomerEntersQty
        ,A.TotalPriceAdjustment
        ,@CreatedBy
    FROM @OrderProductAttributes A
    INNER JOIN @TempOrderDetails B ON A.ProductId = B.ProductID
    WHERE [OrderProductAttributeId] = 0;

    --Updating OrderProductAttributes
    UPDATE [dbo].[OrderProductAttributes]
    SET [OrderDetailId] = A.OrderDetailId
        ,[ProductId] = A.ProductId
        ,[AttributeId] = A.AttributeId
        ,[Product_ProductAttribute_MappingId] = A.Product_ProductAttribute_MappingId
        ,[ProductAttributeValueId] = A.ProductAttributeValueId
        ,[AttributeType] = A.AttributeType
        ,[AttributeName] = A.AttributeName
        ,[Name] = A.Name
        ,[PriceAdjustment] = A.PriceAdjustment
        ,[IsSubscription] = A.IsSubscription
        ,[Quantity] = A.Quantity
        ,[CustomerEntersQty] = A.CustomerEntersQty
        ,[TotalPriceAdjustment] = A.TotalPriceAdjustment
        ,[UpdatedOn] = GETDATE()
    FROM @OrderProductAttributes A
    INNER JOIN OrderProductAttributes B ON A.OrderProductAttributeId = B.Id
    WHERE A.OrderDetailId IN (
            SELECT OrderDetailId
            FROM @OrderDetails
            WHERE UpdateType = 1
            );

    --Insert Into [dbo].[OrderDetailPromotions]
    INSERT INTO [dbo].[OrderDetailPromotions] (
        [OrderDetailId]
        ,[ProductId]
        ,[PromotionId]
        ,[UniquePromotionId]
        ,[PromotionTypeId]
        ,[PromotionPrice]
        ,[ClassificationLevel]
        )
    SELECT ODP.OrderDetailId
        ,ODP.ProductId
        ,ODP.PromotionId
        ,ODP.UniquePromotionId
        ,ODP.PromotionTypeId
        ,ODP.PromotionPrice
        ,ODP.[ClassificationLevel]
    FROM @OrderDetailPromotions ODP
    INNER JOIN @OrderDetails A ON ODP.OrderDetailId = A.OrderDetailId
        AND UpdateType <> 2;-- If order detail will not in removed status.

    IF NOT EXISTS (
            SELECT 1
            FROM OrderDetails
            WHERE OrderDetails.OrderId = @OrderID
            ) -- We have an empty shopping cart
    BEGIN
        DELETE
        FROM Orders
        WHERE OrderId = @OrderID;
    END
    ELSE
    BEGIN
        -- If shopping cart is not empty, add order promotion.
        --Insert Into [dbo].[OrderPromotions]
        INSERT INTO [dbo].[OrderPromotions] (
            [OrderId]
            ,[PromotionId]
            ,[PromotionTypeId]
            ,[UniquePromotionId]
            ,[PromotionCode]
            ,[PromotionAmt]
            )
        SELECT OP.OrderID
            ,OP.PromotionId
            ,OP.PromotionTypeId
            ,OP.UniquePromotionId
            ,OP.PromotionCode
            ,OP.PromotionAmt
        FROM @OrderPromotions OP
    END

    COMMIT TRANSACTION ShopCT_TRAN;
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION ShopCT_TRAN;

    SELECT @ErrMsg = ERROR_MESSAGE()
        ,@ErrSeverity = ERROR_SEVERITY();

    RAISERROR (
            @ErrMsg
            ,@ErrSeverity
            ,1
            )
END CATCH;

结束

任何形式的帮助都是显而易见的。我试图将第二个选择语句排除在 if 条件之外并尝试 catch 块。在那种情况下，它正在工作。或者我可以插入单独的查询

标签： sqlsql-server

Answer 1

SET @OrderId = CONVERT(INT, SCOPE_IDENTITY());
...
--Deleting from OrderPromotion
    DELETE
    FROM [dbo].[OrderPromotions]
    WHERE [OrderId] = @OrderID;-- Removing exist Order Promotion for current order  

一切正常。我的猜测是您认为@OrderID这里有旧的原始值，而它已经更改为新的 id。

要解决这个问题，请将这一行重写为（并且您在这里并不需要显式转换）：

SET @NewOrderId = SCOPE_IDENTITY();

并@NewOrderId用于插入和@OrderID删除。

注意，在回滚 CATCH 块之前，您应该检查是否有打开的事务。文档建议通过检查XACT_STATE()值来做到这一点