带参数的CONTAINSTABLE查询

Question

我想我太慢了，我有个疑问：

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;

它在MSSQL中工作，如果我这样做，中继器将被填充：

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);

但是，当我尝试用参数替换搜索文本时，它不会给出结果：

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)");

我做错了什么？

Answer 1

搜索条件作为一个整体可以被“参数化”：

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))");

编辑#1:应该避免 AddWithValue，因为它会产生计划缓存污染。请阅读本文更好地理解这方面的Server性能。相反，我将使用Add方法(cmd.Parameters.Add)。

+

当参数长度未正确指定时，查询性能和计划缓存问题