How to use a column name in CONTAINSTABLE for the search condition?

Question

I'm surprised to find that neither CONTAINS or CONTAINSTABLE seems to support syntax like the following where you pass a column name in for the last Search Condition parameter.

SELECT *
FROM dbo.Articles AS a
WHERE EXISTS
(
   SELECT *
   FROM dbo.Terms AS t
   INNER JOIN CONTAINSTABLE(dbo.Articles, (ArticleBody), t.FulltextTerm)
      AS ct ON ct.[Key] = a.ArticleId
)

The above query returns an "Incorrect syntax near 't'" error message.

The Terms table contains multiple rows with a FulltextTerm column, and if any of those FulltextTerm values is in the ArticleBody, it should be a match so that particular Article is selected. This is what I'm trying to achieve.

CONTAINS and CONTAINSTABLE appear to only support string literals or variables for the Search Condition parameter, which is very limiting. If that's the only option, it requires a lot more code and will certainly be much slower if I need to iterate thru the Terms table with a cursor or loop.

Am I missing a trick here, or any workarounds someone can suggest - preferably a set-based solution, i.e. avoiding loops.

Answer 1

What about merging all your terms in one variable, and then using the CONTAINSTABLE, as below:-

declare @term as table(
    FulltextTerm nvarchar(60)
)

insert into @term values ('light NEAR aluminum')
insert into @term values ('lightweight NEAR aluminum')

Declare @FulltextTerm nvarchar(max)=''
select @FulltextTerm=@FulltextTerm+' OR ('+FulltextTerm+')' from @term
set @FulltextTerm=SUBSTRING(@FulltextTerm,5,99999)


-- @FulltextTerm will have the below value:-
-- (light NEAR aluminum) OR (lightweight NEAR aluminum)

SELECT *
FROM dbo.Articles AS a
INNER JOIN  
   CONTAINSTABLE (dbo.Articles,ArticleBody,@FulltextTerm) AS ct 
   ON ct.[Key] = a.ArticleId

off course in your case you dont need the table variable @term, you could replace it with your Term table, but I only used it here to show the idea.

I believe this may be better than looping.

Note: I dont know the database version you have but you could even use the below if you can use STRING_AGG function

select @FulltextTerm=STRING_AGG('('+FulltextTerm+')',' OR ') from @term