please help me convert SP to dynamic sql query or to SP which uses COALESCE and it runs ok

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.ShippingFirs开发者_Python百科tName + ' ' + 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

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

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.

---

Apert from the over complication your problem seems to be in:-

   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)) 

It should read something like:

AND ( o.OrderStatusID = COALESCE(@OrderStatusPending, o.OrderStatusID) 
or  o.OrderStatusID =COALESCE(@OrderStatusProcessing, o.OrderStatusID) 
or  o.OrderStatusID =COALESCE(@OrderStatusComplete, o.OrderStatusID) 
or  o.OrderStatusID =COALESCE(@OrderStatusCancelled, o.OrderStatusID) 
or  o.OrderStatusID =COALESCE(@OrderStatusCancelledCustomerRequest, o.OrderStatusID) 
or  o.OrderStatusID =COALESCE(@OrderStatusCancelledDiscontinued, o.OrderStatusID) 
or  o.OrderStatusID =COALESCE(@OrderStatusCancelledPendingNeverPaid, o.OrderStatusID))

Howver as all your @ variables are set to null this is just a complex way of saying

AND o.OrderStatusID = o.OrderStatusID