>
> hi adam thanks for the reply.
>
> i am building a denormalised table to facilitate a full text

search.

> i need an SP to query this table based on a search string which the
> user submits. but i also want to order the results according to
> ranking before returning any rows. so if a row contains both "joe"
> and "bloggs" it will appear further up the results set.
>
> the best way that i could see to tackle this problem was using 2
> stored procedures.
>
> SP_search would be reponsible for querying the datatable and adding
> data to a dummy 'hits' integer column which will keep a tally of

how

> many hits there are (e.g. "joe" and "bloggs" equals 2 hits) this
> kind of thing:
>
> for
> select * from searchindex where text containing :inputparam1 or

text

> containing :inputparam2 ...etc...

containing is probably not a good idea here. Containing queries can
not use an index, which means the entire searchindex table will need
to be read. When searchindex contains a lot of records, this will
take a lot of resources (and time) to complete.

This is how I would attack such a problem.

Take for example a library where you want to build a search on a book
title.

Whenever you add a book, you need to split the title into each word.

You have some tables

Book (ID, Title, AuthorID, etc)

You use an insert trigger to populate a words table based on the
title. It only needs to add new records to this table if it is infact
a word not in any other book.

Words (ID, Value)

You then have another table to link books to words.

BookWord (BookID, WordID)

This is really simplified, you would need to have exception tables so
words like "The" dont influence search results.

You could then use the stored procedure that separated out words from
the input text to come up with particular words records, which you
can then join to BookWord and then Book.

I am sure without too much heartache you could do some sort of
ranking system using order by count(*) or something like that.

Adam