Which conditional statement do I use?

Is there a way to convert the following "multi-condition" SELECT statement into a "single-condition" SELECT statement? I need it to be one condition so it can be used inside a Stored Procedure. The problem I am having is the number of "AND" conditions can change.

Code:

---

SELECT pcSearchFields_Products.idProduct
FROM pcSearchFields_Products
WHERE
pcSearchFields_Products.idSearchData = 3 AND
pcSearchFields_Products.idSearchData = 4

---

In place of the example "AND" conditions I tried to use one statement of "IN (3,4)". However, "IN" returns true if any one of the values match whereas I need them all to match.

Anyone have any ideas? I suspect this is simple for someone experienced with stored procedures.

First of all how can you have two values in the same column

WHERE pcSearchFields_Products.idSearchData = 3 AND
pcSearchFields_Products.idSearchData = 4

If you do not know number of parameters that will be passed, then you need to build your SQL string dynamically and execute it.

Thank you! I googled and found some information on dynamic stored procedures. That will do it.

However, as you pointed out the query was flawed anyway. The following query is actually what I need to do. I would appreciate it if you had a suggestion. Essentially, I need to select all the products ids that are in each group. Here is what I have:

Code:

---

SELECT pcSearchFields_Products.idProduct FROM pcSearchFields_Products WHERE pcSearchFields_Products.idSearchData>0
AND pcSearchFields_Products.idProduct IN
        (
        SELECT pcSearchFields_Products.idProduct
        FROM pcSearchFields_Products
        WHERE pcSearchFields_Products.idSearchData IN (3,4)
        )
AND pcSearchFields_Products.idProduct IN
        (
        SELECT pcSearchFields_Products.idProduct
        FROM pcSearchFields_Products
        WHERE pcSearchFields_Products.idSearchData IN (4,5)
        )
;

---

This code does work. It returns the product id of "4" because it is in both groups. It would be nice if I could lump all the numbers into one statement and use something like "IN". Unfortunately, "IN" and "Exists" return true if there is one match. I need to make sure the product id in all the groups.