SQL Server 2005 -Join based on criteria in table column

Using SQL Server 2005, what is the most efficient way to join the two tables in the following scenario ?

The number of records in each table could be fairly large about 200000 say.

The only way I can currently think of doing this is with the use of cursors and some dynamic SQL for each item which will clearly be very inefficient.

I have two tables - a PERSON table and a SEARCHITEMS table. The SEARCHITEM开发者_StackOverflow中文版S table contains a column with some simple criteria which is to be used when matching records with the PERSON table. The criteria can reference any column in the PERSON table.

For example given the following tables :

PERSON table

PERSONID   FIRSTNAME LASTNAME GENDER AGE ... VARIOUS OTHER COLUMNS
1          Fred      Bloggs   M      16
....
200000     Steve     Smith    M      18

SEARCHITEMS table

ITEMID         DESCRIPTION      SEARCHCRITERIA 
1              Males            GENDER = 'M'
2              Aged 16          AGE=16
3              Some Statistic   {OTHERCOLUMN >= SOMEVALUE AND OTHERCOLUMN < SOMEVALUE}
....
200000         Males Aged 16    GENDER = 'M' AND AGE = 16

RESULTS table should contain something like this :

ITEMID         DESCRIPTION       PERSONID    LASTNAME

1              Males             1           Bloggs
1              Males             200000      Smith
2              Aged 16           1           Bloggs
....
200000         Males Aged 16     1           Bloggs

It would be nice to be able to just do something like

INSERT INTO RESULTSTABLE
  SELECT * 
  FROM PERSON P
  LEFT JOIN SEARCHITEMS SI ON (APPLY SI.SEARCHCRITERIA TO P)

But I can't see a way of making this work. Any help or ideas appreciated.

Seeing that the SEARCHITEMS table is non-relational by nature, it seems that the cursor and dynamic SQL solution is the only workable one. Of course this will be quite slow and I would "pre-calculate" the results to make it somewhat bearable.

To do this create the following table:

CREATE TABLE MATCHEDITEMS(
    ITEMID int NOT NULL
       CONSTRAINT fkMatchedSearchItem 
       FOREIGN KEY 
       REFERENCES SEARCHITEMS(ITEMID), 
    PERSONID int 
       CONSTRAINT fkMatchedPerson 
       FOREIGN KEY 
       REFERENCES PERSON(PERSONID)
       CONSTRAINT pkMatchedItems 
       PRIMARY KEY (ITEMID, PERSONID)
       )

The table will contain a lot of data, but considering it only stores 2 int columns the footprint on disk will be small.

To update this table you create the following triggers:

• a trigger on the SEARCHITEMS table which will populate the MATCHEDITEMS table whenever a rule is changed or added.
• a trigger on the PERSON table which will run the rules on the updated or added PERSON records.

Results can then simply be presented by joining the 3 tables.

SELECT m.ITEMID, m.DESCRIPTION, m.PERSONID, p.LASTNAME
FROM MATCHEDITEMS m
JOIN PERSON p
  ON m.PERSONID = p.PERSONID
JOIN SEARCHITEMS s
  ON m.ITEMID = s.ITEMID 

You could build your TSQL dynamically, and then execute it with sp_executesql.