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?