请帮助我修复某些SP或构建动态sql [英] Please help me to fix some of this SP or to build a dynamic sql

问题描述

你好-
我有一个SP，它的当前格式无法正常工作，它不会返回所有行，并且当我包含一些值时也不会返回任何值.
这是SP:

Hello -
I have a SP which in current format doesn''t work properly, it doesn''t return all the rows and when I am including some values doesn''t return any values.
Here is the SP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AdvancedSearch]
(
@StartTime datetime = null,
@EndTime datetime = null,
@CustomerEmail nvarchar(255) = null,
@Username nvarchar(255) = null,
@CustomerName nvarchar(255) = null,
@OrderNumber int = null,
@MinimumOrderAmount decimal = null,
@MaximumOrderAmount decimal = null,
@ShippingMethod nvarchar(255) = null,
@SKU nvarchar(255) = null,
@CouponID int = null,
@DiscountType int = null,
@ShippingCountryID int = null,
@UserRegistration nvarchar(255) = null,
@OrderStatusPending int = null,
@OrderStatusProcessing int = null,
@OrderStatusComplete int = null,
@OrderStatusCancelled int = null,
@OrderStatusCancelledDiscontinued int = null,
@OrderStatusCancelledCustomerRequest int = null,
@OrderStatusCancelledPendingNeverPaid int = null
    )
     AS
    BEGIN
    SET NOCOUNT ON

    SELECT DISTINCT o.OrderID, o.OrderTotal, n.Name AS OrderStatus, p.Name AS PaymentStatus, s.Name AS ShippingStatus, o.BillingFirstName + '' '' + o.BillingLastName AS CustomerName, o.CreatedOn AS CreatedOn FROM Nop_Order o
    LEFT OUTER JOIN StatusOrders n ON o.OrderStatusID = n.OrderStatusID
    LEFT OUTER JOIN StatusPayments p ON o.PaymentStatusID = p.PaymentStatusID
    LEFT OUTER JOIN ShippingStatus s ON o.ShippingStatusID = s.ShippingStatusID
    LEFT OUTER JOIN Customer c ON o.CustomerID = c.CustomerID
    LEFT OUTER JOIN Discount d ON o.DiscountID = d.DiscountID
    LEFT OUTER JOIN OrderProductionVariable opv ON o.OrderID = opv.OrderID
    LEFT OUTER JOIN ProductionVariations pv ON opv.ProductVariantID = pv.ProductVariantId
    WHERE (o.CreatedOn > @StartTime OR @StartTime IS NULL)
    AND (o.CreatedOn < @EndTime OR @EndTime IS NULL)
    AND (o.ShippingEmail = @CustomerEmail OR @CustomerEmail IS NULL)
    AND (o.OrderStatusID IN (CAST(@OrderStatusID as int)) OR @OrderStatusID IS NULL)
    AND (o.PaymentStatusID IN (@PaymentStatusID) OR @PaymentStatusID IS NULL)
    AND (c.Username = @Username OR @Username IS NULL)
    AND (o.BillingFirstName + '' '' + o.BillingLastName = @CustomerName OR @CustomerName IS NULL)
    AND (o.ShippingFirstName + '' '' + o.ShippingLastName = @CustomerName OR @CustomerName IS NULL)
    AND (o.OrderID = @OrderNumber OR @OrderNumber IS NULL)
    AND (o.OrderTotal > @MinimumOrderAmount or @MinimumOrderAmount IS NULL)
    AND (o.OrderTotal < @MaximumOrderAmount OR @MaximumOrderAmount IS NULL)
    AND (o.ShippingMethod = @ShippingMethod OR @ShippingMethod IS NULL)
    AND (d.DiscountTypeID = @DiscountType OR @DiscountType IS NULL)
    AND (o.ShippingCountryID = @ShippingCountryID OR @ShippingCountryID IS NULL)
    AND (o.DiscountID = @CouponID OR @CouponID IS NULL)
    AND (pv.SKU = @SKU OR @SKU IS NULL)
    AND (c.Email = @UserRegistration OR @UserRegistration IS NULL)
    AND (o.Deleted = 0)
AND (o.OrderStatusID = @OrderStatusPending OR o.OrderStatusID = @OrderStatusProcessing OR o.OrderStatusID = @OrderStatusComplete OR o.OrderStatusID = @OrderStatusCancelled OR o.OrderStatusID = @OrderStatusCancelledDiscontinued
OR o.OrderStatusID = @OrderStatusCancelledCustomerRequest OR o.OrderStatusID = @OrderStatusCancelledPendingNeverPaid)
    ORDER BY o.OrderID
    END

我尝试使用COALESCE进行操作，但是COALESCE似乎无法与INT一起使用，至少在我的SP中不起作用，只要我具有COALESCE和int值，SP就不会返回任何值.
这是带有COALESCE的SP:

I tried something with COALESCE instead but COALESCE doesn''t seems to work with INT, at least not in my SP, whenever I have COALESCE and int value, the SP doesn''t return any value.
Here is the SP with COALESCE:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AdvancedSearch]
(
    @StartTime datetime = null,
    @EndTime datetime = null,
    @CustomerEmail nvarchar(255) = null,
    @Username nvarchar(255) = null,
    @CustomerName nvarchar(255) = null,
    @OrderNumber int = null,
    @MinimumOrderAmount decimal = null,
    @MaximumOrderAmount decimal = null,
    @ShippingMethod nvarchar(255) = null,
    @SKU nvarchar(255) = null,
    @CouponID int = null,
    @DiscountType int = null,
    @ShippingCountryID int = null,
    @UserRegistration nvarchar(255) = null,
    @OrderStatusPending int = null,
    @OrderStatusProcessing int = null,
    @OrderStatusComplete int = null,
    @OrderStatusCancelled int = null,
    @OrderStatusCancelledDiscontinued int = null,
    @OrderStatusCancelledCustomerRequest int = null,
    @OrderStatusCancelledPendingNeverPaid int = null
    )
     AS
    BEGIN
    SET NOCOUNT ON

    SELECT DISTINCT o.OrderID, o.OrderTotal, n.Name AS OrderStatus, p.Name AS PaymentStatus, s.Name AS ShippingStatus, o.BillingFirstName + '' '' + o.BillingLastName AS CustomerName, o.CreatedOn AS CreatedOn FROM Nop_Order o
        LEFT OUTER JOIN StatusOrders n ON o.OrderStatusID = n.OrderStatusID
    LEFT OUTER JOIN StatusPayments p ON o.PaymentStatusID = p.PaymentStatusID
    LEFT OUTER JOIN ShippingStatus s ON o.ShippingStatusID = s.ShippingStatusID
    LEFT OUTER JOIN Customer c ON o.CustomerID = c.CustomerID
    LEFT OUTER JOIN Discount d ON o.DiscountID = d.DiscountID
    LEFT OUTER JOIN OrderProductionVariable opv ON o.OrderID = opv.OrderID
    LEFT OUTER JOIN ProductionVariations pv ON opv.ProductVariantID = pv.ProductVariantId
    WHERE (o.CreatedOn > COALESCE(@StartTime, ''01-01-1899''))
    AND (o.CreatedOn < COALESCE(@EndTime, ''01-01-2099''))
    AND (o.ShippingEmail = COALESCE(@CustomerEmail, o.ShippingEmail))
    AND (c.Username = COALESCE(@Username, c.Username))
    AND (o.BillingFirstName + '' '' + o.BillingLastName = COALESCE(@CustomerName, o.BillingFirstName + '' '' + o.BillingLastName))
    AND (o.ShippingFirstName + '' '' + o.ShippingLastName = COALESCE(@CustomerName, o.ShippingFirstName + '' '' + o.ShippingLastName))
    AND (o.OrderID = COALESCE(@OrderNumber, o.OrderID))
    AND (o.OrderTotal > COALESCE(@MinimumOrderAmount, o.OrderTotal))
    AND (o.OrderTotal < COALESCE(@MaximumOrderAmount, o.OrderTotal))
    AND (o.ShippingMethod = COALESCE(@ShippingMethod, o.ShippingMethod))
    AND (d.DiscountTypeID = COALESCE(@DiscountType, d.DiscountTypeID))
    AND (o.ShippingCountryID = COALESCE(@ShippingCountryID, o.ShippingCountryID))
    AND (o.DiscountID = COALESCE(@CouponID, O.DiscountID))
    AND (pv.SKU = COALESCE(@SKU, pv.SKU))
    AND (c.Email = COALESCE(@UserRegistration, c.Email))
    AND (o.Deleted = 0)
    AND(o.OrderStatusID =
    COALESCE(@OrderStatusPending, o.OrderStatusID)
    | COALESCE(@OrderStatusProcessing, o.OrderStatusID)
    | COALESCE(@OrderStatusComplete, o.OrderStatusID)
    | COALESCE(@OrderStatusCancelled, o.OrderStatusID)
    | COALESCE(@OrderStatusCancelledCustomerRequest, o.OrderStatusID)
    | COALESCE(@OrderStatusCancelledDiscontinued, o.OrderStatusID)
    | COALESCE(@OrderStatusCancelledPendingNeverPaid, o.OrderStatusID))
    ORDER BY o.OrderID

    END

我真的很想看看这个查询是否有效，我遇到的最大问题是订单状态.该网站可能发送了多个选择，我需要将所有选择合并并过滤结果.
我真的希望有人可以帮助我修复此SP或动态查询.
非常感谢.

I would really want to see this query working, the biggest problem I have is with the order status. There might be multiple selections sent from the website, and I need to merge all of them and filter the results.
I really hope someone can help me with either fixing this some of this SP or with dynamic query.
Thanks a lot.

推荐答案

据我乍看，IN条件将无法以这种方式工作.您可能需要阅读有关表值参数 [

As far as I can see at a first glance the IN condition will not work this way. You may want to read something about Table valued parameter [^]to use IN condition. And you will find many examples on this if you google for it.

You can send multiple values (selections) using that parameter and you can use IN condition to get all rows.

See if it helps.

这篇关于请帮助我修复某些SP或构建动态sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！