3

Does anyone know if it is possible to join to a free text table to query for many search terms in one operation vs. having to cursor over?

For example if I have a function that gets the top 1 closest match using FreeTextTable:

create function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL (
     @classCodeDescription  varchar(4000)
    ,@state                 varchar(10)
    ,@classCode             int
    ,@effectiveOn           datetime2)
returns table as
return 

select KEY_TBL.RANK, cd.CLASS_CODE_DESCRIPTION_ID
from CLASS_CODE_DESCRIPTION cd
  join CLASS_CODE cc on cc.CLASS_CODE_ID = cd.CLASS_CODE_ID
  inner join freetexttable (
      CLASS_CODE_DESCRIPTION, DESCRIPTION, @classCodeDescription, 1) as KEY_TBL 
      on cd.CLASS_CODE_DESCRIPTION_ID = KEY_TBL.[KEY]   
where cc.CLASS_CODE = @classCode
  and cc.STATE = @state
  and cc.EFFECTIVE_DATE <= @effectiveOn
  and cc.EXPIRATION_DATE > @effectiveOn
  and cd.EFFECTIVE_DATE <= @effectiveOn
  and cd.EXPIRATION_DATE > @effectiveOn

I then have a table that I would like to join against, to avoid having to do a painfully slow cursor through (there can be thousands upon thousands of rows)

declare @tbl table (
 classCode      int not null
,description    varchar(255) not null
,state          varchar(10) not null default 'FL'
,effectiveOn    datetime2 not null default '1999-01-01 00:00:00')

insert into @tbl (classCode, description) 
values (9410, 'Municipal Employees'), (6, 'Farm: Fish Hatchery & Drivers')

I then do the query:

select *
from @tbl t
  cross apply dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL
  (t.description, t.state, t.classCode, t.effectiveOn)

But receive the error:

Msg 4129, Level 16, State 1, Line 13 The inline function "dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL" cannot take correlated parameters or subqueries because it uses a full-text operator.

I have tried it several different ways, but ultimately I am unable to trick out the engine because they all really boil down to the same thing under the covers.

Is there a way to run FreeTextTable in a set operation vs. having to cursor through it?

1

Hmm... try this "trick" for your function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL to correlate with a SQL table variable rather than the freetext query:

create function FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL (
     @classCodeDescription  varchar(4000)
    ,@state                 varchar(10)
    ,@classCode             int
    ,@effectiveOn           datetime2)
RETURNS @ClosestClassCodeDesc TABLE   
(  
     TBLRANK INT,
     CLASS_CODE_DESCRIPTION_ID  varchar(4000)
)  
as
begin
    insert into @ClosestClassCodeDesc (TBLRANK, CLASSname)
    select KEY_TBL.RANK, cd.CLASS_CODE_DESCRIPTION_ID from CLASS_CODE_DESCRIPTION cd
  join CLASS_CODE cc on cc.CLASS_CODE_ID = cd.CLASS_CODE_ID
  inner join freetexttable (CLASS_CODE_DESCRIPTION, DESCRIPTION, @classCodeDescription, 1) as KEY_TBL on cd.CLASS_CODE_DESCRIPTION_ID = KEY_TBL.[KEY]   
where cc.CLASS_CODE = @classCode
  and cc.STATE = @state
  and cc.EFFECTIVE_DATE <= @effectiveOn
  and cc.EXPIRATION_DATE > @effectiveOn
  and cd.EFFECTIVE_DATE <= @effectiveOn
  and cd.EXPIRATION_DATE > @effectiveOn
return  
end

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.