title: Second insert statement not inserting data
body: <p>I have two insert statement in my SQL query. however it insert the data for first statement only. it does not inserting Data for second insert statement.</p>
  
  <pre><code>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 &lt;&gt; ''
                  )
          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 &lt;&gt; 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;
  </code></pre>
  
  <p>END
  GO</p>
  
  <p>Any kind of help will be appreciable.I have tried to put the second select stament out of if condition and try catch block. in that case it was working. or I can insert with separate query</p>