Search Problem in SQL Server

Question

I need to search in a table for items which have all of my desired values in a column i.e. I have table :

ID :    1 2 3 3 2 2 2 1 1 3
VALUE : 5 6 5 3 6 7 2 1 9 0

I want to give a StoredProc a list of values for example ("6,7,2") and it returns me all IDs that have all the given values in this case it would only returns 2

If I wanted to search for those which at least have one of the values I know I could use "IN" but to have all the values i found nothing.

Thank you in advance

Afshin Arefi

Answer 1

In SQL Server 2008 you can use table value parameters.

These allow you to pass in a table of values to a stored procedure and treat it as any other table (use in sub-queries, joins etc).

In terms of the query - if you do use a table value parameter, you can query it for size (how many rows), use IN in conjunction with a GROUP BY on the ID field and a HAVING that counts the number of rows.