Stored procedure won't work with IN clause parameter? 2

[1DMF]

Hi,

Is there a reason I cannot get my stored procedure to work with an input parameter that is a CSV and used for an IN clause?

PROCEDURE [dbo].[spRPT_DocsQueueHistSuper] 
	-- Add the parameters for the stored procedure here
	@startDate char(12),
	@endDate char(12),
	@members varchar(MAX)
		
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
SELECT  Docs_Request_Hist.Case_ID, Docs_Request_Hist.Sent_By, Docs_Request_Hist.Docs_Present, Docs_Request_Hist.Date_Sent, [firstname] + ' ' + [LastName] AS Adviser, CompanyName, CNames AS ClientName, Prod_Type,Adv_MemNo,Category,Status,Rec_Type
FROM ((Docs_Request_Hist LEFT JOIN Business_Register ON Docs_Request_Hist.Case_ID = Business_Register.Rec_ID) LEFT JOIN Members ON Business_Register.Adv_MemNo = Members.ID)
GROUP BY [FirstName] + ' ' + [LastName], CompanyName,Date_Sent,Case_ID,Sent_By,Docs_Present,CNames,Prod_Type,id,Category,Status,Rec_Type,Adv_Memno
HAVING (Date_Sent Between @startDate And @endDate) AND (Adv_MemNo IN (@members))
ORDER BY [FirstName] + ' ' + [LastName];

END

It always returns zero records when I know there are records for the @members IN clause CSV list parameter?

your help is appreciated.

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

Thought it worth mentioning that the SplitCSV function works with a Join against the items column regardless of the join column's data type, which was handy as I needed the function for an 'IN' clause against a list of numbers and it worked without any modification.

Thanks for everyones help with this, it has been most helpful and enlightening

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads