FreeTextTable Query vs. Cursor in SQL Server Full-Text Search

full-text-searchsql serversql-server-2012t-sql

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?

Best Answer

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
Related Question