how to pass this information to SQL Server 2008?

Question

Please visit http://www.stumbleupon.com/

It asks you what interests you.

I would have a similar thing on my website except I would need to have 4 of such non-identical blocks. When a user has not selected anything in a section, it means s/he has selected all of them. 1 of these blocks would have around 10 options while the other 3 would have 2-3 options each.

How do I pass what all a user has selected to SQL Server 2008? Please note I would have 4 sets of multiple params, and these 4 sets are basically 4 different columns in the row all containing integer id's. If and only if one param per section was passed, I would have done something like

select * from table where column1 = @param1, column2 = @param2 and so on.

but that is not the case, because a user could pass multiple values for @param1 and so on.

I can get this done, but I want to know the most efficient way.

If my post is not clear, please let me know.

EDIT: Basically, if I am storing articles in the database, I have 4 columns in the row, each integer. And I want to query these articles based on these 4 columns, but the catch is to pass the multiple values for these columns NOT just one value per column.

Answer 1

Try dumping all parameter values into the XML, then pass it to SQL Server, then parse it in the query.

Answer 2

If your client language supports it, you should look into table value parameters:

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

if you can't use those, look into one on Arrays and Lists in TSQL:

http://www.sommarskog.se/arrays-in-sql.html

this is a very comprehensive list of dynamic search conditions in TSQL:

http://www.sommarskog.se/dyn-search.html

this may also help:

http://stackoverflow.com/questions/772777/sane-fast-method-to-pass-variable-parameter-lists-to-sqlserver2008-stored-procedu/774067#774067