Using the IN keyword with Parameters

• rich-936142

  Newbie

  Points: 5

  More actions

  November 10, 2008 at 1:42 pm

  #215092

  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.

• Ken McKelvey

  SSCoach

  Points: 18975

  More actions

  November 11, 2008 at 3:09 am

  #896827

  You will have to do a COUNT. Something like:

  SELECT DISTINCT

  &nbsp&nbsp&nbsp&nbspC.CategoryID

  &nbsp&nbsp&nbsp&nbsp,P.*

  &nbsp&nbsp&nbsp&nbsp,F.FeatureValueID

  FROM tblProduct P

  &nbsp&nbsp&nbsp&nbspJOIN tblProductFeatureValue F

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON P.ProductID = F.ProductID

  &nbsp&nbsp&nbsp&nbspJOIN tblProductCategory C

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON P.ProductID = C.ProductID

  &nbsp&nbsp&nbsp&nbspJOIN

  &nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT F1.ProductID

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM tblProductFeatureValue F1

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHERE F1.FeatureValueID IN

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT X1.item.value('@value','int')

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM @XMLDoc.nodes('/root/item') X1(Item)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspGROUP BY F1.ProductID

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspHAVING COUNT(*) =

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT COUNT(X2.item.value('@value','int'))

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM @XMLDoc.nodes('/root/item') X2(Item)

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp)

  &nbsp&nbsp&nbsp&nbsp) D

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON P.ProductID = D.ProductID

  WHERE IsDiscontinued <> 'True'

  &nbsp&nbsp&nbsp&nbspAND C.CategoryID = 1

  ps You may want to experiment with passing a delimited string and using a split string function (plenty on this site) as the overhead for XML can be quite high.