Alter PROCEDURE uspSearchTransaction
@txtTID varchar(50) = null ,
@txtMID varchar(50) = null,
@txtCardNo varchar(50) = null,
@txtFromDate varchar(50) = null,
@txtToDate varchar(50) = null,
@txtAccountNo varchar(50) = null,
@txtFromAmount varchar(50) = null,
@txtToAmount varchar(50) = null,
@ddlStatus varchar(50) = null,
@dllType varchar(50) = null,
@ddlOrderBy varchar(50) = null,
@ddlOrder varchar(50) = null
AS
BEGIN
Declare @status varchar(20)
if(@ddlStatus='0')
Begin
set @status = (select Settlement_Flag from POS_TRANSACTION_OUT)
end
else
begin
set @status=@ddlStatus
end
Declare @type varchar(20)
if(@dllType='0')
Begin
set @type = (select Card_Type from POS_TRANSACTION_OUT)
end
else
begin
set @type=@dllType
end
if (((@txtFromAmount=null) or (@txtFromAmount=''))and ((@txtToAmount=null) or(@txtToAmount='')))
begin
SELECT top 10 POSMerchantMaster.Term_id as TID,
POSMerchantMaster.EntityID as MID,
POS_TRANSACTION_OUT.Merchant_Name,
POS_TRANSACTION_OUT.Card_No,
POSMerchantMaster.MerCrAccNo,
POS_TRANSACTION_OUT.Auth_Id,
POS_TRANSACTION_OUT.Card_Type,
POS_TRANSACTION_OUT.Txn_Amount as Amount ,
POS_TRANSACTION_OUT.Comm_Percentage_Rate,
POS_TRANSACTION_OUT.Comm_Amount,
clearing_txn.card_type,
POS_TRANSACTION_OUT.Uploaded_On as CreditDate,
POS_TRANSACTION_OUT.File_Name,
POS_TRANSACTION_OUT.Settlement_Flag
FROM POSMerchantMaster
inner JOIN POS_TRANSACTION_OUT
ON POSMerchantMaster.EntityID = POS_TRANSACTION_OUT.Entity_Id
inner JOIN clearing_txn
ON POSMerchantMaster.EntityID = clearing_txn.Entity_Id
where (POSMerchantMaster.Term_id LIKE '%'+@txtTID+'%') and
(POSMerchantMaster.EntityID LIKE '%'+@txtMID+'%') and
(POS_TRANSACTION_OUT.Card_No LIKE '%'+@txtCardNo+'%') and
(POS_TRANSACTION_OUT.Uploaded_On BETWEEN @txtFromDate AND @txtToDate) and
(POS_TRANSACTION_OUT.Settlement_Flag like ( @status))and
(POS_TRANSACTION_OUT.Card_Type like ( @type))
ORDER BY + @ddlOrderBy + ' ' + @ddlOrder
end
else
begin
SELECT top 10 POSMerchantMaster.Term_id as TID,
POSMerchantMaster.EntityID as MID,
POS_TRANSACTION_OUT.Merchant_Name,
POS_TRANSACTION_OUT.Card_No,
POSMerchantMaster.MerCrAccNo,
POS_TRANSACTION_OUT.Auth_Id,
POS_TRANSACTION_OUT.Card_Type,
POS_TRANSACTION_OUT.Txn_Amount as Amount ,
POS_TRANSACTION_OUT.Comm_Percentage_Rate,
POS_TRANSACTION_OUT.Comm_Amount,
clearing_txn.card_type,
POS_TRANSACTION_OUT.Uploaded_On as CreditDate,
POS_TRANSACTION_OUT.File_Name,
POS_TRANSACTION_OUT.Settlement_Flag
FROM POSMerchantMaster
inner JOIN POS_TRANSACTION_OUT
ON POSMerchantMaster.EntityID = POS_TRANSACTION_OUT.Entity_Id
inner JOIN clearing_txn
ON POSMerchantMaster.EntityID = clearing_txn.Entity_Id
where (POSMerchantMaster.Term_id LIKE '%'+@txtTID+'%') and
(POSMerchantMaster.EntityID LIKE '%'+@txtMID+'%') and
(POS_TRANSACTION_OUT.Card_No LIKE '%'+@txtCardNo+'%') and
(POS_TRANSACTION_OUT.Uploaded_On BETWEEN @txtFromDate AND @txtToDate) and
(POS_TRANSACTION_OUT.Txn_Amount BETWEEN @txtFromAmount AND @txtToAmount) and
(POS_TRANSACTION_OUT.Settlement_Flag like ( @status))and
(POS_TRANSACTION_OUT.Card_Type like ( @type))
ORDER BY + @ddlOrderBy + ' ' + @ddlOrder
end
END
While execute the sp getting the error 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' how i will fix the error