FreeTextTable - 查詢作為集合操作與必須游標

有誰知道是否可以加入自由文本表以在一次操作中查詢多個搜尋詞而不是必須將游標懸停在上面？

例如，如果我有一個使用 FreeTextTable 獲得前 1 個最接近匹配的函式：

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

然後我有一個我想加入的表，以避免不得不通過一個痛苦的緩慢游標（可能有成千上萬的行）

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')

然後我進行查詢：

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

但收到錯誤：

消息 4129，級別 16，狀態 1，第 13 行內聯函式“dbo.FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL”不能採用相關參數或子查詢，因為它使用全文運算符。

我已經嘗試了幾種不同的方法，但最終我無法欺騙引擎，因為它們實際上都歸結為同一件事。

有沒有辦法在集合操作中執行 FreeTextTable 而不是必須通過游標來執行？

嗯…為您的函式 FN_GET_CLOSEST_CLASS_CODE_DESCRIPTION_ID_TBL 嘗試這個“技巧”，以與 SQL 表變數而不是自由文本查詢相關聯：

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

引用自：https://dba.stackexchange.com/questions/149458