sp_helptext and CONTAINS

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi!

This might be simple but I just do not get it. :(

I've created a SP that takes a set of parameters, builds an SQL string
and executes it using sp_executesql. One of the parameters to my SP,
called @freetext, is used to query word documents in an IMAGE column
using CONTAINS. Using any of the other parameters works well, but
using my @freetext param always end up with no found records, even
though I know I have matching documents. Executing the corresponding
query from Query analyzer works well, so the fulltext index and search
mechanism seems to be set up ok.

I also have another SP doing the same thing against another table but
with a more "brute force" approach, building the SQL string and
executing it using sp_executesql with only the SQL string as
parameter. This works fine, so there is something with the use of all
3 params to sp_executesql that I do not grasp.

The SP's signature looks like this:
CREATE PROCEDURE Document_Criteria_LIST(
@DocId INT,
@DocTypeId INT = NULL,
@ProjectId INT = NULL,
@ClientCompanyId INT = NULL,
@IndustryId INT = NULL,
@ProductId INT = NULL,
@ContactClientId INT = NULL,
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL,
@Freetext VARCHAR(100) = NULL)

My 'params' string is created like this:

SET @Params = ' @DocId INT,
@DocTypeId INT =NULL,
@ProjectId INT =NULL,
@ClientCompanyId INT =NULL,
@IndustryId INT =NULL,
@ProductId INT =NULL,
@ContactClientId INT =NULL,
@FromDate DATETIME =NULL,
@ToDate DATETIME =NULL,
@Freetext VARCHAR(100) =NULL'


At the end of the SP my local variable @Sql contains this:

SELECT d.DocumentId,
d.DocumentName,
d.Title,
d.Source,
d.ContactClientId,
d.CreatedDate,
d.CreatedBy,
d.ChangedDate,
d.ChangedBy,
d.DocDate,
'ContactName'=c1.LastName + ', ' + c1.FirstName
FROM Document_TB d
LEFT OUTER JOIN Client_TB c1
ON d.ContactClientId = c1.ClientId
WHERE DocDate BETWEEN @FromDate AND @ToDate
AND DocumentType_CD IS NULL AND
CONTAINS(DocumentBlob, '"*@Freetext*"')
ORDER BY d.CreatedDate, d.ChangedDate

And the final call to sp_executesql looks like this:
EXEC sp_executesql @Sql, @Params, @DocId = @DocId,
@DocTypeId = @DocTypeId,
@ProjectId = @ProjectId,
@ClientCompanyId = @ClientCompanyId,
@IndustryId = @IndustryId,
@ProductId = @ProductId,
@ContactClientId = @ContactClientId,
@FromDate = @FromDate,
@ToDate = @ToDate,
@Freetext = @Freetext

Is it not possible to use the CONTAINS predicate like this in
conjunction with this kind of call to sp_executesql?

Regards,

Jonas

.

Relevant Pages