title: T-SQL filtering on dynamic name-value pairs
body: <p>I'll describe what I am trying to achieve:</p>
  
  <p><strong>I am passing down to a SP an xml with name value pairs that I put into a table variable, let's say <code>@nameValuePairs</code>. 
  I need to retrieve a list of IDs for expressions (a table) with those exact match of name-value pairs (attributes, another table) associated.</strong> </p>
  
  <p>This is my schema:</p>
  
  <p><strong>Expressions table</strong> --> (expressionId, attributeId)</p>
  
  <p><strong>Attributes table</strong> --> (attributeId, attributeName, attributeValue)</p>
  
  <p>After trying complicated stuff with dynamic SQL and evil cursors (which works but it's painfully slow) this is what I've got now:</p>
  
  <pre><code>--do the magic plz!
  
  -- retrieve number of name-value pairs
  SET @noOfAttributes = select count(*) from @nameValuePairs
  
  select distinct
      e.expressionId, a.attributeName, a.attributeValue
  into 
      #temp
  from 
      expressions e
  join
      attributes a
  on
      e.attributeId = a.attributeId
  join --&gt; this join does the filtering
      @nameValuePairs nvp
  on 
      a.attributeName = nvp.name and a.attributeValue = nvp.value
  group by
      e.expressionId, a.attributeName, a.attributeValue
  
  -- now select the IDs I need
  -- since I did a select distinct above if the number of matches
  -- for a given ID is the same as noOfAttributes then BINGO!
  select distinct 
      expressionId
  from 
      #temp
  group by expressionId
  having count(*) = @noOfAttributes 
  </code></pre>
  
  <p>Can people please review and see if they can spot any problems? Is there a better way of doing this?</p>
  
  <p>Any help appreciated!</p>