Using the IN keyword with Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using the IN keyword with Parameters

Author

Topic

richie2838
Starting Member

2 Posts

Posted - 2008-11-10 : 16:55:13

Hey all,

I have a stored procedure in which one of the parameters being passed to it contains an XML string in which i have a (random) number of
elements. Within each of these elements i have a value attribute which contains an ID which i would like to filter my result set on. Please take a look at the following code:

Declare @XMLDoc XML;
set @XMLDoc= '<root><item value="46" /><item value="50" /></root >';

SELECT * FROM
(
SELECT DISTINCT tblProductCategory.CategoryID, tblProduct.*, tblProductFeatureValue.FeatureValueID
FROM tblProduct INNER JOIN tblProductFeatureValue ON tblProduct.ProductID = tblProductFeatureValue.ProductID INNER JOIN
tblProductCategory ON tblProduct.ProductID = tblProductCategory.ProductID
WHERE IsDiscontinued <> 'True'
AND tblProductCategory.CategoryID = 1
) AS Q1 WHERE FeatureValueID IN
(
select x.item.value('@value','int') from @XMLDoc.nodes('/root/item') as x(item)

Now the above code works ok, it will return me all products who have a FeatureValueID of 46 OR 50.

What i need is a method which will return all products who have a FeatureValueID of 46 AND 50.

Any ideas would be most welcome.

Thanks in advance.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-10 : 17:24:58

That's an invalid argument, you can't not have a column that is equal to both 46 and 50. Think of A = 1 or A=2 but A can't be both 1 and 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 23:21:09

may be what you're looking for is this

SELECT CategoryID FROM
(
SELECT DISTINCT tblProductCategory.CategoryID, tblProduct.*, tblProductFeatureValue.FeatureValueID
FROM tblProduct INNER JOIN tblProductFeatureValue ON tblProduct.ProductID = tblProductFeatureValue.ProductID INNER JOIN
tblProductCategory ON tblProduct.ProductID = tblProductCategory.ProductID
WHERE IsDiscontinued <> 'True'
AND tblProductCategory.CategoryID = 1 
) AS Q1 WHERE FeatureValueID IN
( 
select x.item.value('@value','int') from @XMLDoc.nodes('/root/item') as x(item) 
) 
GROUP BY CategoryID
HAVING COUNT(DISTINCT FeatureValueID)=2

richie2838
Starting Member

2 Posts

Posted - 2008-11-11 : 03:33:31

Hey guys,

Thanks for your replies, its much appreciated.

The number of <item> elements that is passed into the xml is completely variable. It could be 1, or it could be 20. The script essentially brings back a list of products who have certain attributes e.g.

tblProduct - ProductID = 1 (A Watch)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 1 (Gents Watch)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 2 (Quartz)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 3 (Over £50)

The <item> element in the XML is used to pass in the FeatureValueID, the user can build up a selection of these, i.e, give me all (Gents)(Quartz) Watches that are (Over £50)

The table relations are 1 product -> many ProductFeatures.

I hope this maybe explains things a bit better. I had tried using dynamic sql and the EXISTS keyword

WHERE EXISTS (SELECT * FROM tblProductFeatureValue WHERE ProductID = 1 AND FeatureValueID = 1)
AND EXISTS (SELECT * FROM tblProductFeatureValue WHERE ProductID = 1 AND FeatureValueID = 2)

This seemed to work ok, but is really inefficient.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 12:26:19

Say if you have


tblProduct - ProductID = 1 (A Watch)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 1 (Gents Watch)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 2 (Quartz)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 3 (Over £50)
tblProductFeatureValue - ProductID = 1, FeatureValueID = 4 (Silver Color)

If I search for All(Gents)(Quartz)Watches that are (Over £50) will this product be listed??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources