|
Create Procedure [dbo].[Proc_GenericSearch_CustomerOrders]
@CustomerId int=null,
@TransctionStatus int=null,
@FromDT datetime = null,
@ToDT datetime = null,
@EmailID varchar(250) = null,
@MobileNumber varchar(250) = null
as
begin
select a.CustomerID,a.CreatedDatetime,b.Insta_transaction_id,B.Insta_id,
b.Insta_payments_id,a.CreatedBy,a.EName,a.DateofBirth,a.MobileNo,
a.EmailID,b.CustomerOrderID,b.Insta_status,
c.OrderStatus,c.OrderDate,c.PaymentCategoryCode
from TxnCustomers a
inner join
TxnPaymentOrderDetailsResponse b on a.CustomerID=b.CustomerID
LEFT join TxnCustomerOrders c on b.CustomerOrderID=c.CustomerOrderID
where
PaymentGateway='PAYTM' AND ReconciliationStatus not in(1)
AND
(ReconciliationStatus = @TransctionStatus OR @TransctionStatus IS NULL) --put every condition like
this to handle Generic Search where
null parameter values are allowed
AND
(b.EntryDate >= @FromDT OR @FromDT IS NULL)
AND
(B.EntryDate<=@ToDT OR @ToDT IS NULL)
AND
(A.EmailID=@EmailID OR @EmailID IS NULL)
AND
(A.MobileNo=@MobileNumber OR @MobileNumber IS NULL)
end
|
