Блог

Я не могу понять, почему мое значение игнорируется. В моей хранимой процедуре есть эти два оператора AND

 AND (@Current IS NULL OR (o.[OrderStatusId] <> 40) AND (o.[OrderStatusId] <> 30) AND ( NOT ((o.[ShippingStatusId] IN (30, 40)) AND (o.[PaymentStatusId] IN (30, 35, 40)))))
    AND (@Printed IS NULL OR o.[Printed] = @Printed)
 

@Printed Значение немного совпадает с @Current , и передается в хранимую процедуру точно так же, как @Current

Но напечатанное значение не выводит результаты, как будто оно ничего не делает, хотя я знаю, что результаты должны отличаться.

Я преобразую запрос linq в хранимую процедуру, поэтому я знаю, что результаты должны отличаться.

Вот часть запроса linq, которая используется.

 if (current.HasValue amp;amp; current.Value)
    query = query.Where(o => o.OrderStatusId != (int)OrderStatus.Cancelled amp;amp; o.OrderStatusId != (int)OrderStatus.Complete amp;amp; !((o.ShippingStatusId == (int)ShippingStatus.Shipped || o.ShippingStatusId == (int)ShippingStatus.Delivered) amp;amp; o.Printed amp;amp; (o.PaymentStatusId == (int)PaymentStatus.Paid || o.PaymentStatusId == (int)PaymentStatus.PartiallyRefunded || o.PaymentStatusId == (int)PaymentStatus.Refunded)));

if (printed.HasValue)
    query = query.Where(o => printed.Value == o.Printed);

 

Есть идеи

Обновить:

Просматривая запрос из linq в отладчике vs, я вижу следующий запрос, но я не эксперт по БД, поэтому я немного растерялся, глядя на этот.

 {SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[OrderGuid] AS [OrderGuid], 
    [Project2].[StoreId] AS [StoreId], 
    [Project2].[CustomerId] AS [CustomerId], 
    [Project2].[BillingAddressId] AS [BillingAddressId], 
    [Project2].[ShippingAddressId] AS [ShippingAddressId], 
    [Project2].[PickUpInStore] AS [PickUpInStore], 
    [Project2].[OrderStatusId] AS [OrderStatusId], 
    [Project2].[ShippingStatusId] AS [ShippingStatusId], 
    [Project2].[PaymentStatusId] AS [PaymentStatusId], 
    [Project2].[PaymentMethodSystemName] AS [PaymentMethodSystemName], 
    [Project2].[CustomerCurrencyCode] AS [CustomerCurrencyCode], 
    [Project2].[CurrencyRate] AS [CurrencyRate], 
    [Project2].[CustomerTaxDisplayTypeId] AS [CustomerTaxDisplayTypeId], 
    [Project2].[VatNumber] AS [VatNumber], 
    [Project2].[OrderSubtotalInclTax] AS [OrderSubtotalInclTax], 
    [Project2].[OrderSubtotalExclTax] AS [OrderSubtotalExclTax], 
    [Project2].[OrderSubTotalDiscountInclTax] AS [OrderSubTotalDiscountInclTax], 
    [Project2].[OrderSubTotalDiscountExclTax] AS [OrderSubTotalDiscountExclTax], 
    [Project2].[OrderShippingInclTax] AS [OrderShippingInclTax], 
    [Project2].[OrderShippingExclTax] AS [OrderShippingExclTax], 
    [Project2].[PaymentMethodAdditionalFeeInclTax] AS [PaymentMethodAdditionalFeeInclTax], 
    [Project2].[PaymentMethodAdditionalFeeExclTax] AS [PaymentMethodAdditionalFeeExclTax], 
    [Project2].[TaxRates] AS [TaxRates], 
    [Project2].[OrderTax] AS [OrderTax], 
    [Project2].[OrderDiscount] AS [OrderDiscount], 
    [Project2].[OrderTotal] AS [OrderTotal], 
    [Project2].[RefundedAmount] AS [RefundedAmount], 
    [Project2].[RewardPointsWereAdded] AS [RewardPointsWereAdded], 
    [Project2].[CheckoutAttributeDescription] AS [CheckoutAttributeDescription], 
    [Project2].[CheckoutAttributesXml] AS [CheckoutAttributesXml], 
    [Project2].[CustomerLanguageId] AS [CustomerLanguageId], 
    [Project2].[AffiliateId] AS [AffiliateId], 
    [Project2].[CustomerIp] AS [CustomerIp], 
    [Project2].[AllowStoringCreditCardNumber] AS [AllowStoringCreditCardNumber], 
    [Project2].[CardType] AS [CardType], 
    [Project2].[CardName] AS [CardName], 
    [Project2].[CardNumber] AS [CardNumber], 
    [Project2].[MaskedCreditCardNumber] AS [MaskedCreditCardNumber], 
    [Project2].[CardCvv2] AS [CardCvv2], 
    [Project2].[CardExpirationMonth] AS [CardExpirationMonth], 
    [Project2].[CardExpirationYear] AS [CardExpirationYear], 
    [Project2].[AuthorizationTransactionId] AS [AuthorizationTransactionId], 
    [Project2].[AuthorizationTransactionCode] AS [AuthorizationTransactionCode], 
    [Project2].[AuthorizationTransactionResult] AS [AuthorizationTransactionResult], 
    [Project2].[CaptureTransactionId] AS [CaptureTransactionId], 
    [Project2].[CaptureTransactionResult] AS [CaptureTransactionResult], 
    [Project2].[SubscriptionTransactionId] AS [SubscriptionTransactionId], 
    [Project2].[PaidDateUtc] AS [PaidDateUtc], 
    [Project2].[ShippingMethod] AS [ShippingMethod], 
    [Project2].[Printed] AS [Printed], 
    [Project2].[PrintedOnUtc] AS [PrintedOnUtc], 
    [Project2].[IsSupport] AS [IsSupport], 
    [Project2].[EditedStatusId] AS [EditedStatusId], 
    [Project2].[Deleted] AS [Deleted], 
    [Project2].[Id1] AS [Id1]
    FROM ( SELECT 
        CASE WHEN (([Extent1].[Printed] <> 1) AND (N'Express Delivery' = [Extent1].[ShippingMethod])) THEN N'1' ELSE N'0' END AS [C1], 
        CASE WHEN ([Extent1].[Printed] <> 1) THEN CASE WHEN (([Extent1].[ShippingMethod] IN (N'Fast Delivery',N'My Point Pickup')) AND ( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[GenericAttribute] AS [Extent3]
            WHERE (N'WarehouseOverride' = [Extent3].[Key]) AND (N'Order' = [Extent3].[KeyGroup]) AND ([Extent3].[EntityId] = [Extent1].[Id])
        ))) THEN N'0' ELSE N'1' END ELSE N'0' END AS [C2], 
        [Extent1].[Id] AS [Id], 
        [Extent1].[OrderGuid] AS [OrderGuid], 
        [Extent1].[StoreId] AS [StoreId], 
        [Extent1].[CustomerId] AS [CustomerId], 
        [Extent1].[BillingAddressId] AS [BillingAddressId], 
        [Extent1].[ShippingAddressId] AS [ShippingAddressId], 
        [Extent1].[PickUpInStore] AS [PickUpInStore], 
        [Extent1].[OrderStatusId] AS [OrderStatusId], 
        [Extent1].[ShippingStatusId] AS [ShippingStatusId], 
        [Extent1].[PaymentStatusId] AS [PaymentStatusId], 
        [Extent1].[PaymentMethodSystemName] AS [PaymentMethodSystemName], 
        [Extent1].[CustomerCurrencyCode] AS [CustomerCurrencyCode], 
        [Extent1].[CurrencyRate] AS [CurrencyRate], 
        [Extent1].[CustomerTaxDisplayTypeId] AS [CustomerTaxDisplayTypeId], 
        [Extent1].[VatNumber] AS [VatNumber], 
        [Extent1].[OrderSubtotalInclTax] AS [OrderSubtotalInclTax], 
        [Extent1].[OrderSubtotalExclTax] AS [OrderSubtotalExclTax], 
        [Extent1].[OrderSubTotalDiscountInclTax] AS [OrderSubTotalDiscountInclTax], 
        [Extent1].[OrderSubTotalDiscountExclTax] AS [OrderSubTotalDiscountExclTax], 
        [Extent1].[OrderShippingInclTax] AS [OrderShippingInclTax], 
        [Extent1].[OrderShippingExclTax] AS [OrderShippingExclTax], 
        [Extent1].[PaymentMethodAdditionalFeeInclTax] AS [PaymentMethodAdditionalFeeInclTax], 
        [Extent1].[PaymentMethodAdditionalFeeExclTax] AS [PaymentMethodAdditionalFeeExclTax], 
        [Extent1].[TaxRates] AS [TaxRates], 
        [Extent1].[OrderTax] AS [OrderTax], 
        [Extent1].[OrderDiscount] AS [OrderDiscount], 
        [Extent1].[OrderTotal] AS [OrderTotal], 
        [Extent1].[RefundedAmount] AS [RefundedAmount], 
        [Extent1].[RewardPointsWereAdded] AS [RewardPointsWereAdded], 
        [Extent1].[CheckoutAttributeDescription] AS [CheckoutAttributeDescription], 
        [Extent1].[CheckoutAttributesXml] AS [CheckoutAttributesXml], 
        [Extent1].[CustomerLanguageId] AS [CustomerLanguageId], 
        [Extent1].[AffiliateId] AS [AffiliateId], 
        [Extent1].[CustomerIp] AS [CustomerIp], 
        [Extent1].[AllowStoringCreditCardNumber] AS [AllowStoringCreditCardNumber], 
        [Extent1].[CardType] AS [CardType], 
        [Extent1].[CardName] AS [CardName], 
        [Extent1].[CardNumber] AS [CardNumber], 
        [Extent1].[MaskedCreditCardNumber] AS [MaskedCreditCardNumber], 
        [Extent1].[CardCvv2] AS [CardCvv2], 
        [Extent1].[CardExpirationMonth] AS [CardExpirationMonth], 
        [Extent1].[CardExpirationYear] AS [CardExpirationYear], 
        [Extent1].[AuthorizationTransactionId] AS [AuthorizationTransactionId], 
        [Extent1].[AuthorizationTransactionCode] AS [AuthorizationTransactionCode], 
        [Extent1].[AuthorizationTransactionResult] AS [AuthorizationTransactionResult], 
        [Extent1].[CaptureTransactionId] AS [CaptureTransactionId], 
        [Extent1].[CaptureTransactionResult] AS [CaptureTransactionResult], 
        [Extent1].[SubscriptionTransactionId] AS [SubscriptionTransactionId], 
        [Extent1].[PaidDateUtc] AS [PaidDateUtc], 
        [Extent1].[ShippingMethod] AS [ShippingMethod], 
        [Extent1].[Printed] AS [Printed], 
        [Extent1].[PrintedOnUtc] AS [PrintedOnUtc], 
        [Extent1].[IsSupport] AS [IsSupport], 
        [Extent1].[EditedStatusId] AS [EditedStatusId], 
        [Extent1].[Deleted] AS [Deleted], 
        [Extent2].[Id] AS [Id1]
        FROM  [dbo].[Order] AS [Extent1]
        LEFT OUTER JOIN [dbo].[RewardPointsHistory] AS [Extent2] ON ([Extent2].[UsedWithOrder_Id] IS NOT NULL) AND ([Extent1].[Id] = [Extent2].[UsedWithOrder_Id])
        WHERE ([Extent1].[Deleted] <> 1) AND (40 <> [Extent1].[OrderStatusId]) AND (30 <> [Extent1].[OrderStatusId]) AND ( NOT (([Extent1].[ShippingStatusId] IN (30,40)) AND ([Extent1].[Printed] = 1) AND ([Extent1].[PaymentStatusId] IN (30,35,40))))
    )  AS [Project2]
    ORDER BY [Project2].[C1] DESC, [Project2].[Printed] ASC, [Project2].[C2] DESC, [Project2].[CreatedOnUtc] DESC}
 

Вот моя хранимая процедура, на которую вы можете посмотреть:

 USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[OrderLoad]
    -- Add the parameters for the stored procedure here
    @OrderId int = null,
    @CustomerId int = null,
    @WarehouseId int = null,
    @BillingCountryId int = null,
    @PaymentMethodSystemName nvarchar(max) = null,
    @OrderStatusId int = null,
    @PaymentStatusId int = null,
    @ShippingStatusId int = null,
    @BillingEmail nvarchar(max) = null,
    @BillingFirstName nvarchar(max) = null,
    @BillingLastName nvarchar(max) = null,
    @PurchasedPreviously bit = null,
    @Printed bit = null,
    @OrderNotes nvarchar(max) = null,
    @ZendeskId nvarchar(max) = null,
    @RexCode nvarchar(max) = null,
    @Current bit = null,
    @Challenge bit = null,
    @ShippingMethod nvarchar(max) = null,
    @EditedStatus int = null,
    @OrderGuid nvarchar(max) = null,
    @SupportReason int = null,
    @CreatedFromUtc datetime = null,
    @CreatedToUtc datetime = null,
    @IsSupport bit = null,
    @PageIndex int = 0, 
    @PageSize int = 2147483644,
    @TotalRecords int = null OUTPUT
AS
BEGIN
    DECLARE
        @sql nvarchar(max)
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    create table #TempTotal (RowNum int identity(1,1), id int);

    --select all
    INSERT INTO #TempTotal ([id])
        SELECT o.[Id] 
        FROM [Test].[dbo].[Order] o with (NOLOCK)

     -- Another alternative to bypass left join, which works with pagination
    LEFT join [Test].[dbo].[Address] a on a.Id = o.BillingAddressId and (
        coalesce(@BillingEmail,'') <> ''
        or coalesce(@BillingFirstName,'') <> ''
        or coalesce(@BillingLastName,'') <> ''
        or coalesce(@BillingCountryId,'') <> ''
    )

    WHERE
        o.[Deleted] = 0
    /*AND (@BillingEmail IS null OR a.[Email] LIKE '%'   @BillingEmail   '%')
    AND (@BillingFirstName IS null OR a.[FirstName] LIKE '%'   @BillingFirstName   '%')
    AND (@BillingLastName IS null OR a.[LastName] LIKE '%'   @BillingLastName   '%')
    AND (@BillingCountryId IS null OR a.[CountryId] = @BillingCountryId)
    */
    AND (@Printed IS NULL OR o.[Printed] = @Printed)

    AND (@Current IS NULL OR (o.[OrderStatusId] <> 40) AND (o.[OrderStatusId] <> 30) AND ( NOT ((o.[ShippingStatusId] IN (30, 40)) /*AND (o.[Printed] = '1')*/ AND (o.[PaymentStatusId] IN (30, 35, 40)))))

    AND (@IsSupport IS null OR o.[IsSupport] = @IsSupport)

    --paging
    DECLARE @PageLowerBound int
    SET @PageLowerBound = @PageSize * @PageIndex

    -- Return the paged records
    select [Id] --note select * can produce unexpected results
      ,[OrderGuid]
      ,[RexOrderId]
      ,[StoreId]
      ,[CustomerId]
      ,[BillingAddressId]
      ,[ShippingAddressId]
      ,[PickUpInStore]
      ,[OrderStatusId]
      ,[ShippingStatusId]
      ,[PaymentStatusId]
      ,[PaymentMethodSystemName]
      ,[CustomerCurrencyCode]
      ,[CurrencyRate]
      ,[CustomerTaxDisplayTypeId]
      ,[VatNumber]
      ,[OrderSubtotalInclTax]
      ,[OrderSubtotalExclTax]
      ,[OrderSubTotalDiscountInclTax]
      ,[OrderSubTotalDiscountExclTax]
      ,[OrderShippingInclTax]
      ,[OrderShippingExclTax]
      ,[PaymentMethodAdditionalFeeInclTax]
      ,[PaymentMethodAdditionalFeeExclTax]
      ,[TaxRates]
      ,[OrderTax]
      ,[OrderDiscount]
      ,[OrderTotal]
      ,[RefundedAmount]
      ,[RewardPointsWereAdded]
      ,[CheckoutAttributeDescription]
      ,[CheckoutAttributesXml]
      ,[CustomerLanguageId]
      ,[AffiliateId]
      ,[CustomerIp]
      ,[AllowStoringCreditCardNumber]
      ,[CardType]
      ,[CardName]
      ,[CardNumber]
      ,[MaskedCreditCardNumber]
      ,[CardCvv2]
      ,[CardExpirationMonth]
      ,[CardExpirationYear]
      ,[AuthorizationTransactionId]
      ,[AuthorizationTransactionCode]
      ,[AuthorizationTransactionResult]
      ,[CaptureTransactionId]
      ,[CaptureTransactionResult]
      ,[SubscriptionTransactionId]
      ,[PaidDateUtc]
      ,[ShippingMethod]
      ,[Printed]
      ,[Deleted]
      ,[CreatedOnUtc]
      ,[EditedStatusId]
      ,[IsSupport]
      ,[PrintedOnUtc]
    from [Test].[dbo].[Order] ord with (NOLOCK)
    where ord.[Id] in (
      select id
      from #TempTotal tt
    )

    ORDER BY 
    (CASE @IsSupport
    WHEN 0 THEN ord.[Printed]
    END) ASC
,   (CASE @IsSupport
    WHEN 0 THEN ord.[CreatedOnUtc]
    END) DESC
,   (CASE @IsSupport
    WHEN 1 THEN ord.[CreatedOnUtc]
    END) DESC

    --ORDER BY ord.[CreatedOnUtc] DESC
    OFFSET @PageLowerBound ROWS FETCH NEXT @PageSize ROWS ONLY;
    
    --total records
    select @TotalRecords = count(*) from #TempTotal;

    DROP TABLE #TempTotal
END