Query Full-text w/Stored Proc w/parameter

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

SQL Server Development (2000)

Query Full-text w/Stored Proc w/parameter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-27 : 08:52:43

Philco writes "I am using SQL Server 7, and new to full-text catalogs.

I have a full-text catalog on Artists.FName and Artists.LName called Artists_Catalog. In Query Analyzer this works:

select distinct ArtistID, LName, FName
from Artists
WHERE contains(Artists.*, '"jOHN*"')
order by Artists.LName

However, when I try to create a store proc version in which I can pass a string in as the search parameter, it won't compile, error reported at @strQuery in where clause.

CREATE procedure spSearchArtists
@strQuery varchar(128) as
select distinct ArtistID, LName, FName
from Artists
where contains(Artists.*, @strQuery)
order by Artists.LName
return

Not sure where I'm going wrong on the syntax. Can you assist?

Many thanks!
Philco"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 15:07:28

Have you tried using Dynamic SQL to build your SELECT statement?

Here are a couple of links to articles by Merkin...
[url]http://www.sqlteam.com/item.asp?ItemID=4599[/url]
[url]http://www.sqlteam.com/item.asp?ItemID=4619[/url]

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources