Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Problems with multi-select parameter and LIKE

Author  Topic 

bh0526
Yak Posting Veteran

71 Posts

Posted - 2011-05-19 : 08:27:33
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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-19 : 08:53:29
The string for a single item will be 'xxxx'
For multiselect it will be 'xxx,yyy,zzz'
Are you using an SP to retrun the data?
If so I would parse the string and turn it into a table variable of values (use a cte for this) and use that to join to the table to get the result.

You could also use dynamic sql or
where ',' + @str + ',' like '%,' + col + ',%' -- this won't work because you need a like statement - I would go wit het table above.



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -