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.
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. |
|
|
|
|
|
|
|