How do I search a "Property Bag" table in SQL?

Question

I have a basic "property bag" table that stores attributes about my primary table "Card." So when I want to start doing some advanced searching for cards, I can do something like this:

SELECT dbo.Card.Id, dbo.Card.Name
  FROM dbo.Card
         INNER JOIN dbo.CardProperty ON dbo.CardProperty.IdCrd = dbo.Card.Id
 WHERE dbo.CardProperty.IdPrp = 3 AND dbo.CardProperty.Value = 'Fiend'
INTERSECT
  SELECT dbo.Card.Id, dbo.Card.Name
  FROM dbo.Card
         INNER JOIN dbo.CardProperty ON dbo.CardProperty.IdCrd = dbo.Card.Id
 WHERE (dbo.CardProperty.IdPrp = 10 AND (dbo.CardProperty.Value = 'Wind' OR dbo.CardProperty.Value = 'Fire'))

What I need to do is to extract this idea into some kind of stored procedure, so that ideally I can pass in a list of property/value combinations and get the results of the search.

Initially this is going to be a "strict" search meaning that the results must match all elements in the query, but I'd also like to have a "loose" query so that it would match any of the results in the query.

I can't quite seem to wrap my head around this one. My previous version of this was to do generate some massive SQL query to execute with a lot of AND/OR clauses in it, but I'm hoping to do something a little more elegant this time. How do I go about doing this?

Answer 1

it seems to me that you have an EAV model here. if you're using sql server 2005 and up i'd suggest you use XML datatype for this:

http://weblogs.sqlteam.com/mladenp/archive/2006/10/14/14032.aspx

makes searching and stuff much easier with built in xml querying capabilities.

if you can't change your model then look at this:

http://weblogs.sqlteam.com/davidm/articles/12117.aspx