|
k80sg
Starting Member
7 Posts |
 Posted - 11/28/2011 : 02:39:39
|
I have a SP query like this:
FROM [Transact] T
LEFT JOIN [Outlet] O
On (T.Outlet_Code = O.Code)
LEFT JOIN [SystemCode] SC
on (CONVERT(NVARCHAR,T.Mode) = SC.Code)
Where (CardNo In (Select [CardNo] from [Card] where [CardNo] = @CardNo and [DeletedBy] is null and [DeletedOn] is null and [MemberID] = @MemberId))
and ((T.TransactDate Between @TransactDateFrom And @TransactDateTo
or @TransactDateFrom is null or @TransactDateTo is null)
and (T.TransactDate >= @TransactDateFrom or @TransactDateFrom is null)
and ((',' + @ReceiptNo +',' LIKE '%,' + T.ReceiptNo + ',%') or @ReceiptNo is null)
Group by T.AutoID, TransactDate,TransactTime, SC.Name, O.Name, ReceiptNo, AmountSpent, TransactPoints, VoidOn
Basically I have a few receipts records in my database, 2 of them are 'sun29842' and 'wis87364'
From this line:
and ((',' + @ReceiptNo +',' LIKE '%,' + T.ReceiptNo + ',%') or @ReceiptNo is null)
I manage to retrieve both 'sun29842' and 'wis87364' records if my searchstring which is passed in with @ReceiptNo is 'sun29842,wis87364' or just 1 of them if the searchstring is 'sun29842'. It returns no records if it's not the exact string for e.g partial string: 'sun298,wis8736' will return nothing which I would also need to display. Please kindly advice. Thanks. |
Edited by - k80sg on 11/28/2011 02:41:20
|
|
SQL Like with comma seperated
