CONTAINSTABLE query with Parameters into Repeater

Question

I think I'm being very slow, I have a query:

SELECT K.RANK, physicalName, DocText, FileType
FROM Documents AS Docs
INNER JOIN CONTAINSTABLE(
    Documents,DocText, 'ISABOUT (pages Weight(0.7))'
) AS K
ON Docs.DocumentID = K.[KEY]
ORDER BY K.RANK;

It works in MSSQL and if I do this the repeater is filled:

SqlCommand objCommand = new SqlCommand("SELECT K.RANK, physicalName, DocText, FileType FROM Documents AS Docs INNER JOIN CONTAINSTABLE(Documents,DocText, 'ISABOUT ( pages Weight(0.7)                     )') AS K         ON Docs.DocumentID = K.[KEY]         ORDER BY K.RANK", objConn);

BUT when I try and replace the search text with a parameter it gives me no results:

SqlCommand objCommand = new SqlCommand("SELECT K.RANK, physicalName, DocText, FileType FROM Documents AS Docs INNER JOIN CONTAINSTABLE(Documents,DocText, 'ISABOUT ( @SearchParams                         )') AS K         ON Docs.DocumentID = K.[KEY]         ORDER BY K.RANK", objConn);
        objCommand.Parameters.AddWithValue("@SearchParams", "pages Weight(0.7)");

What am I doing wrong?

It seems as if you cannot use parameters here. Construct the search clause manually. You would also have to do that when the user enters more words and you wish to search by these words and not the sentence. — alzaimar, Sep 30 '13 at 18:38
my intention was to parse the input and make that into a single search term. so if the entered search this. I would produce a string "search Weight(0.5), this weight(0.5)". I'm also a little worried about using non-parameterized queries with user input — jimmy, Sep 30 '13 at 18:42
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Sep 30 '13 at 20:01

Bogdan Sahlean · Accepted Answer · 2013-09-30T19:14:48.267

2

The search condition as a whole can be "parameterized":

SqlCommand objCommand = 
    new SqlCommand("SELECT K.RANK, physicalName, DocText, FileType FROM Documents AS Docs     
    INNER JOIN 
    CONTAINSTABLE(Documents,DocText, @SearchCondition) AS K 
    ON Docs.DocumentID = K.[KEY] ORDER BY K.RANK", objConn);

    objCommand.Parameters.AddWithValue("@SearchParams", "ISABOUT (pages Weight(0.7))");

Edit #1: AddWithValue should be avoided because it generates plan cache pollution. Please read this article to better understand this aspect of SQL Server performance. Instead I would use Add method (cmd.Parameters.Add).

+

Query performance and plan cache issues when parameter length not specified correctly

edited Sep 30 '13 at 19:14

answered Sep 30 '13 at 18:43

Bogdan Sahlean

19,233
3
42
57

Thank you, just tested and works! Is there a way (some documentation?) of knowing what can and can not be parametrized? – jimmy Sep 30 '13 at 18:48
@jimmy: Generally speaking, object names/identifier (example: DB's name, table's name, column's name) cannot be "parameterized". And, in some cases the arguments of some build-in functions cannot be "parameterized" (example: [Bulk Insert with filename parameter](http://stackoverflow.com/questions/7306616/bulk-insert-with-filename-parameter/7306741#7306741) ). – Bogdan Sahlean Sep 30 '13 at 19:10
@jimmy: I added a note regarding the impact of `AddWithValue` usage on SQL Server performance. – Bogdan Sahlean Sep 30 '13 at 19:18

CONTAINSTABLE query with Parameters into Repeater

1 Answers1