I have a report parameter, @antibiotics, which presently allows the user to select one value from a dropdown list. I then use this parameter to search a string column using LIKE. So far, this all works great. So my WHERE statement looks like this presently: Where Notes LIKE '%' + @antibiotics + '%' Now I want to allow users to select multiple values for @antibiotics. So I made the parameter multi-select. But now I am lost on how to construct my WHERE statement. I tried using combinations of LIKE and IN but nothing works. It works if I only select one value from my dropdown list. But if I select more than one value, I get a syntax error near "," or something. Does anyone know how I can accomplish this? Please understand that my column "Notes" contains the antibiotic I want to search for but it's imbedded in the middle somewhere such as: "The patient is really sick and we tried ampicillin to cure him" or "The patient was given dopamycin and had a postivie reaction" So on the report, the user could select ampicillin and dopamycin from the multi-select dropdown and the parameter @antibiotics would contain these 2 entries. I provided this simple example because I have posted this problem before and everyone provides examples as if the antibiotic name was not buried in the Notes column but the only value in this column. Thanks, Bob

To achieve this you have to develop a custom code to form the SQL statement as needed.Read the below link on how to create custom code and use it as expression. http://msdn.microsoft.com/en-us/library/ms155798.aspx The steps you should follow is 1. Create custom code to take the multivalue parameter as input and construct the SQL statement inside the function. 2. Use the customcode as expression in your dataset.

Below is a solution using the table-valued split function posted by SQL Server MVP Jeff Model on SQL Server Central (http://www.sqlservercentral.com/articles/Tally+Table/72993/). CREATE TABLE dbo.PatientNotes( PatentNoteID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_PatientNotes PRIMARY KEY ,PatientID int NOT NULL ,Notes varchar(MAX) ); INSERT INTO dbo.PatientNotes VALUES (1,'The patient is really sick and we tried ampicillin to cure him') ,(2,'The patient was given dopamycin and had a postivie reaction') ,(3,'The patient given both dopamycin and ampicillin') ,(4,'The patient was given no medication'); DECLARE @AntibioticList AS varchar(8000) = 'dopamycin,ampicillin'; SELECT DISTINCT PatientID ,Notes FROM dbo.PatientNotes AS pn CROSS APPLY dbo.DelimitedSplit8K(@AntibioticList,',') AS items WHERE pn.Notes LIKE '%' + items.Item + '%'; Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Your mult-value parameter generates string in form of Value1, Value2, Value3,... that's why you get error when you try to use it with LIKE. You should then split the value generated by a param to individual values and redefine your query.

Hi Dan, Sorry, I forgot to say in my original post that I am using SQL Server 2005. It looked like you are using a function (dbo.DelimitedSplit8K) that is for SQL 2008? Thanks, Bob

Jeff's function will work on SQL 2005 or above. I successfully ran it on SQL 2005. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/