1

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
TPR
  • 2,567
  • 10
  • 41
  • 65

3 Answers3

1

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:

Sane/fast method to pass variable parameter lists to SqlServer2008 stored procedure

Community
  • 1
  • 1
KM.
  • 101,727
  • 34
  • 178
  • 212
0

Won't such a query work in this case?

SELECT * 
FROM   TABLE 
WHERE  COLUMN1 IN ( @param11, @param12, @param13 ) 
       AND COLUMN2 IN ( @param21, @param22, @param23 )

and so on.

Gidil
  • 4,137
  • 2
  • 34
  • 50
anonymous
  • 1
0

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

Kerido
  • 2,930
  • 2
  • 21
  • 34