Full text search... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Full text search...

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-07 : 14:36:41

Hi there,
i have a Full text search on a table... tblProducts
I am trying to query it, to search the descriptions of products..

this is the code i use (or part of it)

strQuery = "Exec spSearchProducts '"&request("keywords")&"'"
Set rsProducts = Server.CreateObject("ADODB.Recordset")

rsProducts.Open strQuery, conn, 1, 1 'Opened as Read-Only

spSearchProducts:

CREATE PROCEDURE spSearchProducts(

@Query VarChar(100)

)
AS
BEGIN
SELECT * from
tblProducts
WHERE
FREETEXT(ProductDescription, @Query)
END

I get this error:

A variable cannot be used to specify a search condition in a fulltext predicate when accessed through a cursor.

Any idea of how to fix it?

Alex

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:30:48

Create a string variable within the stored procedure to hold the select statement then use exec sp_executesql
example:
declare @@str Nvarchar(255)
set @@str = 'select * from tblproducts where freetext(productdescription,'+@query+' )'
exec sp_executesql
sp_executesql requires the inputs to be unicode.

Raymond Laubert
MCDBA, MCITP:Administration, MCT

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-08 : 17:29:08

Hey,
i have this now:

-----------------------------
CREATE PROCEDURE spSearchProducts(

@Query VarChar(100)

)
AS
declare @@str Nvarchar(255)

BEGIN
set @@str = 'select * from tblproducts
where freetext(productdescription,'+@query+' )'

exec sp_executesql

END
------------------------------------

however i get this error:
Procedure 'sp_executesql' expects parameter '@statement', which was not supplied.

so then, i tried this:
exec sp_executesql @@str

which gave me a different error:
Incorrect syntax near 'TEST'.

TEST was my @Query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources