Variable number of parameters for a stored proc? (SQL Server)

[CrazyCrane]

Hi.

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.

[-TheDarkEye-]

why dont you just send the stored procedure the name of the user (or whatever you use to identify users) and then get the stored procedure to figure out which catagories to get? otherwise, i dont realy understand why youre even bothering to make a stored procedure.

[CrazyCrane]

I don't want to enforce registration for all visitors, so some won't have a DB entry - but I'd still like them to be able to select which categories they're interested in. Hence I'll store their preferences in a cookie, and pass them somehow to the stored procedures.

I use stored procedures for all of the database interaction. They're more efficent (i.e. faster) than simple SQL statements, a lot more secure, more reusable, and easier to write code for.

[westmich]

What about adding a table of user preferences to correspond to the cookies you're setting? Then use the earlier suggestion of having the sproc look-up user data based on the passed user ID from the cookie, i.e. the cookie would only store a unique ID.

[CrazyCrane]

So each user is assigned a different ID, which corresponds to a database entry storing their preferences? Yeah that could work I think. I'll have a proper think and see how feasible that is; my one worry is cleaning up redundant data, but I suppose storing a last accessed time could allow for old data to be removed.

It certainly sounds feasible, thanks a lot. Any other options would of course be welcome .