SQL Server CONTAINS Predicate - Using a column value as <contains_condition>

Question

I have a problem rewriting an existing process that uses the CONTAINS predicate. The existing process works with CURSOR , but it is very slow and slower as more data is added.

I made a simple example (code below) containing two tables: one is Full text with an index , and the other is a column that identifies the CONTAINS condition for selecting rows from the first,

The current stored procedure uses CURSOR to cycle through the second table by setting the @filter variable, and then uses CONTAINS to find the rows from the first table that matches. The problem is that it works for several hours, and worse.

To speed up the process, I tried to use the CONTAINS predicate directly by the column value, and not through the pain of the cursor ... but I get a syntax error. My example is below.

I tried to implement CROSS APPLY , and I also tried to write User-Defined Function ( fnCONTAINS ) without any luck.

 IF ( object_id('Players') IS NOT NULL ) DROP TABLE Players go IF ( object_id('TeamNeeds') IS NOT NULL ) DROP TABLE TeamNeeds go -- create fulltext catalog ft as default go CREATE TABLE Players ( PlayerID INT IDENTITY(1, 1), PlayerName VARCHAR(20), PlayerPositions VARCHAR(60) ) go CREATE UNIQUE INDEX IXPlayerID ON Players( PlayerID ) go CREATE fulltext INDEX ON Players(PlayerPositions) KEY INDEX IXPlayerID go INSERT Players (PlayerName, PlayerPositions) VALUES( 'Patrick Travers', 'Pitcher,Left Field,Center Field,Right Field,Shortstop' ) go CREATE TABLE TeamNeeds ( TeamID INT, Keywords VARCHAR(50) ) go INSERT TeamNeeds (TeamID, Keywords) VALUES( 1, '"Center Field" and "Shortstop" and "Pitcher"' ) go WAITFOR delay '00:00:05' go -- Give the Full Text Index process time to populate the catalog SELECT PlayerID, PlayerName, PlayerPositions FROM Player, TeamNeeds WHERE CONTAINS(PlayerPositions, Keywords) go -- Syntax error on Keywords... SELECT PlayerID, PlayerName, PlayerPositions FROM Players, TeamNeeds WHERE CONTAINS(PlayerPositions, '"Center Field" and "Shortstop" and "Pitcher"') go -- Works just fine, but requires setting an explicit search expression for every search, which is terribly slow 

Answer 1

This is only possible with multiple TVF approvals, as far as I can see.

Create the next function

 CREATE FUNCTION [dbo].[ft_test] (@Keywords VARCHAR(50)) RETURNS @ReturnTable TABLE ( PlayerID INT, PlayerName VARCHAR(20), PlayerPositions VARCHAR(60)) AS BEGIN INSERT INTO @ReturnTable SELECT PlayerID, PlayerName, PlayerPositions FROM Players WHERE CONTAINS(PlayerPositions, @Keywords) RETURN END 

Then the following works perfectly

 SELECT * FROM TeamNeeds CROSS APPLY [dbo].[ft_test] (Keywords) CA 

Although the built-in version failed with an error: "The built-in function" x "cannot accept correlated parameters or subqueries because it uses a full-text operator.