FreetextTable

[beer]

Hello everyone,

It's been a pain to find some helpful tips on T-SQL on the web. I finally came to the thought of the forum, I hope this will help me out.

Here is the headache that I have had for three days now (Or maybe I am stupid enough not to get the problem solved...well)

I am trying to write a freetexttable Stored Procedure, however; it(query analyzer) tells me that there's an incorrect syntax error......

Here is the SP:

CREATE PROCEDURE fulltextsearch
(
@Text1 VARCHAR(8000)
)
AS
declare @sql varchar(8000)

set @sql = 'SELECT Rank, Text1'

set @sql = @sql + 'FROM Articles INNER JOIN freetextTABLE(Articles,Text1,''" '+@Text1+' "'')
AS SR
on ID=[Key]
order by Rank desc'

exec(@sql)

Can anyone tell me what's wrong with it? Your help and your time is highly appreciated.

I have already added a primary key on the table, made the index table, catelog.

Pleae let me know what's wrong.....thank you very much.

Sandy

 

[cdukes]

If you put:

Select @sql

before you execute the SQL string you will find you are trying to execute the following

SELECT Rank, Text1FROM Articles INNER JOIN freetextTABLE(Articles,Text1,'" XYZ "')
AS SR
on ID=[Key]
order by Rank desc

where XYZ is the input string.

a couple of points:

1. you require a space between Text1 and FROM.
2. You do not require all the quotes around XYZ. 'ZYX' will do.
3. As you have aliased the freetextTABLE you will probably also require SR.[Key]

Hope this helps,

Chris Dukes