SQL Server, Full Text Indexing, And ASP.NET Parameterized Queries

I've been driving myself nuts trying to get a sensible product search
going. The existing live site search is just a LIKE %searchterm% on
the Title field in our Products table. Fast, but not great ;) Talks
between IT and Marketing have resulted in this being the desired
results and order:

Exact Title match
Substring Title match
Substring Keywords match
Substring Description match

OK, I can easily do this with UNION queries using LIKE (and a virtual
"weight" column in each query), but the query takes too long. So I'm
trying out SQL Server full text indexing, in an attempt to get the
speed up (and the natural language stuff is just plain cool).

Where I'm running into problems is doing a FULLTEXT match on
Description. It seems that to do a phrase match (e.g. "new york"
should match only Descriptions where the phrase "new york" occurs) I
need to enclose the search term in quotation marks in the query (or
maybe single AND double quotes).

But using parameters in ASP.NET (which I'm supposed to do to avoid SQL
injection attacks, yes?) I don't really have full control of the
quoting - ASP.NET and/or SQL Server automagically quotes strings for
me before passing them into the query. I think.

For example, this doesn't find any description matches:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)

set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"

SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"@theSearchTerm"'))

ORDER BY theWeight DESC, m.ShortName
==========================================

But this one (where I put in the actual string instead of using the
parameter) *does* get the desired results, including matches in the
Description:
==========================================
Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)

set @theSearchTerm = "new york"
set @theSearchTerm1 = "%new york%"

SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName = @theSearchTerm

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight
FROM Product m(NoLock)
WHERE m.ShortName LIKE @theSearchTerm1)

UNION

(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight
FROM Product m(NoLock)
WHERE CONTAINS(*, '"new york"'))

ORDER BY theWeight DESC, m.ShortName
==========================================

Trying various permutations of quotes around the parameter gives me
either syntax errors or undesirable results.


Has anybody tried this sort of thing? How did you do it?

Thanks,

Greg Holmes