x

[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

more ▼

asked Sep 06, 2011 at 04:44 PM in Default

pFab gravatar image

pFab
31 2 3 3

Update :

I have moved it over into a Full Text Search engine - Just trying to work out how to return the relevancy of the word hit from FREETEXT search.
Sep 09, 2011 at 10:47 AM pFab
(comments are locked)
10|1200 characters needed characters left

The question has been closed Sep 09, 2011 at 01:53 PM by Fatherjack for the following reason:


0 answers: sort newest

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x75
x11

asked: Sep 06, 2011 at 04:44 PM

Seen: 789 times

Last Updated: Sep 06, 2011 at 11:13 PM