جستجوی ترکیبی

[HamidNch]

لطفا بهم بگین مشکل این کد کجاست:
من دوتا کنترل برای تعیین تاریخ و دوتا لیست باکس یکی برای تعیین نام بانکها و یکی برای نام کاربران دارم.من میخوام که بسته به انتخاب هایم فیلتر انجام بشه و رکوردهای متناظر با فیلتر نمایش داده بشه.یعنی اگر از لیست باکس بانک موردی انتخابی نشد در عمل سلکت شرکت نکند و همینطور در مورد کاربران و تاریخ نیز این موضوع صدق میکند.
به طور خلاصه من در اولین آیتم لیستباکسهام یه آیتم با نام "انتخاب کنید..." که اگه کاربر اونو انتخاب کرد،اون مورد در عمل فیلترینگ شرکت نکنه.
با تشکر از دوستان.

ALTER PROCEDURE [dbo].[SP_Sport_GetApprovedPaymentsToBanksBySearch]
@startDate DATETIME,
@endDate DATETIME,
@bankIDs NVARCHAR(50),
@userIDs NVARCHAR(MAX)

AS BEGIN

IF( NOT(@bankIDs IS NULL) AND NOT(@userIDs IS NULL))
Begin
SELECT Payment_SettleDate,(SELECT tm.Member_UserName FROM tbl_Membership tm WHERE tm.Member_ID = Payment_UserID) AS Payment_UserName,
Payment_Amount,(SELECT tb.Bank_Name FROM tbl_Banks tb WHERE tb.Bank_ID = Payment_BankID) AS Payment_BankName,Payment_ReservationNumber,
Payment_ReferenceNumber ,Payment_Description
FROM tbl_SportPayments tsp
WHERE (Payment_TransactionState = 'Approved' OR Payment_TransactionState ='Ok' OR Payment_TransactionState = '3')
AND (Payment_ReservationNumber <> '' OR Payment_ReservationNumber <> NULL)
AND ( ((Payment_SettleDate >=@startDate) AND (Payment_SettleDate <= @endDate)) OR ((@startDate IS NULL) AND (@endDate IS NULL)) )
AND ( Payment_BankID IN (SELECT item FROM dbo.fnSplit(@bankIDs, ',')))
AND ( Payment_UserID IN (SELECT item FROM dbo.fnSplit(@userIDs, ',')))
END
ELSE
IF( (@bankIDs IS NULL) AND (@userIDs IS NULL))
BEGIN
SELECT Payment_SettleDate,(SELECT tm.Member_UserName FROM tbl_Membership tm WHERE tm.Member_ID = Payment_UserID) AS Payment_UserName,
Payment_Amount,(SELECT tb.Bank_Name FROM tbl_Banks tb WHERE tb.Bank_ID = Payment_BankID) AS Payment_BankName,Payment_ReservationNumber,
Payment_ReferenceNumber ,Payment_Description
FROM tbl_SportPayments tsp
WHERE (Payment_TransactionState = 'Approved' OR Payment_TransactionState ='Ok' OR Payment_TransactionState = '3')
AND (Payment_ReservationNumber <> '' OR Payment_ReservationNumber <> NULL)
AND ( ((Payment_SettleDate >=@startDate) AND (Payment_SettleDate <= @endDate)) OR ((@startDate IS NULL) AND (@endDate IS NULL)) )
END
ELSE
IF( (@bankIDs IS NULL) AND NOT(@userIDs IS NULL))
BEGIN
SELECT Payment_SettleDate,(SELECT tm.Member_UserName FROM tbl_Membership tm WHERE tm.Member_ID = Payment_UserID) AS Payment_UserName,
Payment_Amount,(SELECT tb.Bank_Name FROM tbl_Banks tb WHERE tb.Bank_ID = Payment_BankID) AS Payment_BankName,Payment_ReservationNumber,
Payment_ReferenceNumber ,Payment_Description
FROM tbl_SportPayments tsp
WHERE (Payment_TransactionState = 'Approved' OR Payment_TransactionState ='Ok' OR Payment_TransactionState = '3')
AND (Payment_ReservationNumber <> '' OR Payment_ReservationNumber <> NULL)
AND ( ((Payment_SettleDate >=@startDate) AND (Payment_SettleDate <= @endDate)) OR ((@startDate IS NULL) AND (@endDate IS NULL)) )
AND ( Payment_UserID IN (SELECT item FROM dbo.fnSplit(@userIDs, ',')))
END
ELSE
IF( NOT(@bankIDs IS NULL) AND (@userIDs IS NULL))
BEGIN
SELECT Payment_SettleDate,(SELECT tm.Member_UserName FROM tbl_Membership tm WHERE tm.Member_ID = Payment_UserID) AS Payment_UserName,
Payment_Amount,(SELECT tb.Bank_Name FROM tbl_Banks tb WHERE tb.Bank_ID = Payment_BankID) AS Payment_BankName,Payment_ReservationNumber,
Payment_ReferenceNumber ,Payment_Description
FROM tbl_SportPayments tsp
WHERE (Payment_TransactionState = 'Approved' OR Payment_TransactionState ='Ok' OR Payment_TransactionState = '3')
AND (Payment_ReservationNumber <> '' OR Payment_ReservationNumber <> NULL)
AND ( ((Payment_SettleDate >=@startDate) AND (Payment_SettleDate <= @endDate)) OR ((@startDate IS NULL) AND (@endDate IS NULL)) )
AND ( Payment_BankID IN (SELECT item FROM dbo.fnSplit(@bankIDs, ',')))
END
END

[Reza_Yarahmadi]

مشکل کدتون چیه؟ جواب نمیده؟ بعضی وقتها جواب میده و بعضی وقتها جواب نمیده؟ نمونه ورودی های SP چیه؟ ...؟
شرطها تون رو از حالت
IF( NOT(@bankIDs IS NULL) AND (@userIDs IS NULL))
به این حالت تغییر بدید ببینید درست میشه یا نه
IF(ISNULL(@bankIDs, '') <> '' AND ISNULL(@userIDs, '') = '')
در ضمن دستور Select خودتون رو هم میتونید بصورت یر تغییر بدید (هم باعث افزایش سرعت اجرا میشه هم خوانایی کدتون رو بالا میبره)
SELECT
tsp.Payment_SettleDate,
tm.Member_UserName AS Payment_UserName,
tsp.Payment_Amount,
tb.Bank_Name AS Payment_BankName,
tsp.Payment_ReservationNumber,
tsp.Payment_ReferenceNumber,
tsp.Payment_Description,
FROM
tbl_SportPayments tsp Left Outer Join tbl_Membership tm
ON
tm.Member_ID = tsp.Payment_UserID
Left Outer Join tbl_Banks tb
ON
tb.Bank_ID = tsp.Payment_BankID