[Closed] Creating ranked results from array parameter

Hi - I hope you can help.

I am creating a search result from a keyword array passed to a param in a stored proc. But I think there must be a better way of working it.

I'm passing the parameters like:

@separator char(1), (usually a space)
@txtkeywords varchar(500), (something like "luxury car buyers")

I am creating a temp table like this :

declare @Result TABLE (
    listid int, 
    score bigint, 
    p bit,
    e bit,
    t bit ,
    f bit,
    s bit,
    i bit,
    listtype varchar(1),
    ListStatusCode int, 
    selected bit,
    CountryINT int,
    CurrencyID int
)

Then I am iterating each word passed in @txtkeywords and inserting into @Result giving a score depending on where and how I find it.

   set @separator = ' '
    while patindex('%' + @separator + '%' , @txtkeywords) <> 0 
    begin
        select @separator_position =  patindex('%' + @separator + '%' , @txtkeywords)
        select @array_value = left(@txtkeywords, @separator_position - 1)
        select @like_text = '%' + @array_value + '%'
        Select @Start_text = @array_value + ' %'
        Select @in_text = '% ' + @array_value + ' %'
        Select @end_text = '% ' + @array_value 

 -- Take each keyword and iterate for score depending on which field it has been found


 INSERT INTO @Result (listid,  score, p, e, t, f, s, i, listtype, ListStatusCode, CountryINT, CurrencyID) 
    SELECT     List.ListId, 1000 AS rankrate, List.postflag, List.emailflag, List.phoneflag, List.faxflag, List.smsflag, List.insertflag, List.ListType, List.ListStatusCode, 
    Company.CountryINT, list.CurrencyID
    FROM         List  with (nolock) INNER JOIN
    Company with (nolock) ON List.CompanyId = Company.CompanyId
    WHERE list.ListName Like @Start_text or list.listname like @end_text or list.listname like @in_text



INSERT INTO @Result (listid,  score, p, e, t, f, s, i, listtype, ListStatusCode, CountryINT, CurrencyID) 
    SELECT     List.ListId, 500 AS rankrate, List.postflag, List.emailflag, List.phoneflag, List.faxflag, List.smsflag, List.insertflag, List.ListType, List.ListStatusCode, 
    Company.CountryINT, list.CurrencyID
    FROM         List  with (nolock) INNER JOIN
    Company  with (nolock) ON List.CompanyId = Company.CompanyId
    WHERE list.ListName Like @like_text



INSERT INTO @Result (listid,  score, p, e, t, f, s, i, listtype, ListStatusCode, CountryINT, CurrencyID) 
    SELECT     List.ListId, 100 AS exrate, List.postflag, List.emailflag, List.phoneflag, List.faxflag, List.smsflag, List.insertflag, List.ListType, List.ListStatusCode, 
    Company.CountryINT, list.CurrencyID
    FROM         List  with (nolock) INNER JOIN
    Company with (nolock) ON List.CompanyId = Company.CompanyId
    WHERE list.description Like @Start_text or list.description like @end_text or list.description like @in_text -- or list.description like @like_text




    select @txtkeywords = stuff(@txtkeywords, 1, @separator_position, '')
    end

I perform something like 8 selects like the above for each word entered to acheive the right result set.

When I have my result table - I select it out and done. It works - but doesn't seem graceful or particularly quick.

Can you suggest a better approach?

Many thanks

Peter