CODEWITHSUNDEEP
sqlsql-serverfull-text-searchsubquery
Mohsen
Mohsen

Reputation: 4266

Subquery containing containstable with a condition from querying table

This query gives me the error:"Incorrect syntax near 'Suggestion'."

SELECT(SELECT COUNT(*) FROM CONTAINSTABLE(Products,keywords,Suggestion)) InventoryRank
FROM (
    SELECT Suggestion FROM aTable
)

How can I give the same result?

Upvotes: 2

Views: 712

Answers (2)

gofr1
gofr1

Reputation: 15987

CONTAINSTABLE cannot work with table column in search condition. You can make it like:

DECLARE @search nvarchar(4000)

SELECT @search = STUFF(( 
    SELECT  '*" or "' + Suggestion 
    FROM aTable
    FOR XML PATH('')
),1,6,'') +'*"'

--That will give you string like "sug1*" or "sug2*" or "sug3*" to search
--You can make whatever you need string. F.e. "sug1" or "sug2" or "sug3"

SELECT COUNT (*) 
FROM CONTAINSTABLE(searched_table,searched_column,@search)

EDIT

If you need a different InventoryRank per Suggestion, you can use dynamic SQL and temp table:

IF OBJECT_ID(N'##temp') IS NOT NULL DROP TABLE ##temp

CREATE TABLE ##temp (
    Suggestion nvarchar(max),
    InventoryRank int
)

DECLARE @sql nvarchar(max)


SELECT @sql = (
    SELECT N'INSERT INTO ##temp SELECT  '''+Suggestion+''' as Suggestion, COUNT (*) as InventoryRank FROM CONTAINSTABLE(searched_table,searched_column,'''+Suggestion+''');' 
    FROM aTable
    FOR XML PATH('')
)
--PRINT(@sql)

EXEC sp_executesql @sql

SELECT *
FROM ##temp

PRINT will give you query:

INSERT INTO ##temp SELECT  'sug1' as Suggestion, COUNT (*) as InventoryRank FROM CONTAINSTABLE(searched_table,searched_column,'sug1');
INSERT INTO ##temp SELECT  'sug2' as Suggestion, COUNT (*) as InventoryRank FROM CONTAINSTABLE(searched_table,searched_column,'sug2');

So you do'nt need a WHILE loop or CURSOR to run through all rows from aTable

Upvotes: 0

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

The 3rd parameter of CONTAINSTABLE must be a literal or variable, it cannot be a column name. If you need the count of each suggestion, you can try the following:

DECLARE @suggestion varchar(100);
DECLARE @result table (Suggestion varchar(100), Result int);
DECLARE csr CURSOR FOR SELECT Suggestion FROM aTable;

FETCH NEXT FROM csr INTO @suggestion;

WHILE @@FETCH_STATUS = 0 BEGIN
    INSERT INTO @result (Suggestion, Result)
        SELECT @suggestion, (SELECT COUNT(*) FROM CONTAINSTABLE(Products,keywords,@suggestion));
    FETCH NEXT FROM csr INTO @suggestion;
END

CLOSE csr;
DEALLOCATE csr;

SELECT * FROM @result;

MSDN CONTAINSTABLE (Transact-SQL) documentation.

Upvotes: 1

Related Questions