Which conditional statement do I use?

[o1webdawg]

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.

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.

[skhanal]

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.

[o1webdawg]

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:

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.