An application I'm writing will have several "categories" - for example, news items may be general news, site news, movie news, book news etc. Likewise articles, reviews etc. may belong to the same categories.
To this end, each news item, article etc. belongs to one or more categories. All well and good. My database therefore has a Categories table, a News table, and a NewsCategories table (and likewise Articles and ArticleCategories etc. etc.)
Now I'd like my visitors to be able to choose which categories they're interested in. I can easily store this in a cookie. My problem lies in using this data meaningfully, because visitors can be interested in a variable number of categories.
So let's say Alan is interested in categories 2, 4 and 5 and Bob is only interested in category 3. How can I pass this info to a stored procedure which can then query the tables and get news stories which fall into the correct categories?
I can't use a string of the form '2, 4, 5' for use in an IN clause (it simply doesn't work, as SQL Server attempts to convert the entire string to an integer). I don't want to have many parameters which might be populated or not, because in future the number of categories might grow.
Any help / advice greatly appreciated. I hope I've explained this well enough - basically it's a question of how to send a stored procedure one or more integers for use in the where clause of a query.
Thanks in advance.