5

MS SQL Server 2005: table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! p.s. am willing to upgrade to sql 2008 if it fixes this :)

CREATE FUNCTION fnt_FullTextSearch ( @s NVARCHAR(4000) )
RETURNS TABLE
AS
  RETURN
    ( SELECT [key], [rank]
        FROM FREETEXTTABLE(table1, *, @s) )

DECLARE @terms TABLE ( term VARCHAR(MAX) )
INSERT INTO @terms VALUES ( 'flu' )
INSERT INTO @terms VALUES ( 'acid' )

-- The inline function "..." cannot 
-- take correlated parameters or subqueries 
-- because it uses a full-text operator.
SELECT ft.[key], ft.[rank]
    FROM @terms 
        CROSS APPLY fnt_FullTextSearch(term) ft

--syntax error on term
SELECT ft.[key], ft.[rank]
    FROM @terms 
        CROSS APPLY FREETEXTTABLE(table1, *, term)
Mike Atlas
  • 8,193
  • 4
  • 46
  • 62
user433342
  • 859
  • 1
  • 7
  • 26

1 Answers1

0

Maybe its because you are missing the alias on the second statement ?

SELECT FT.[key], FT.[rank]
    FROM @terms 
        CROSS APPLY FREETEXTTABLE(table1, *, term) FT

It sounds trivial...but I dont see any other reason why it should fail !

Whimsical
  • 5,985
  • 1
  • 31
  • 39