使用ISABOUT时可以使用变量吗? [英] Can I use a variable when using ISABOUT?

问题描述

我正在尝试使用存储过程来创建一个表，该表通过采用主题名称并使用与该主题名称相关联的关键字和权重来确定帖子的排名方式来对帖子进行排名.我一直在尝试使用CONTAINSTABLE和ISABOUT，但是在将关键字和权重放入ISABOUT语句时遇到了麻烦.我曾尝试将关键字和权重从它们所在的表中转换为varchar变量，然后将该变量放入ISABOUT语句中，但是当我运行SP时，结果表为空，因此我假设该变量不能正常工作，我不确定从这儿去哪里.

I'm trying to use a stored procedure to create a table that ranks posts by taking a topic name and using keywords and weights associated with that topic name to determine how they should be ranked. I've been trying to use CONTAINSTABLE and ISABOUT, but I'm having trouble with putting the keywords and weights into the ISABOUT statement. I've tried converting the keywords and weights from the table they're in into a varchar variable, and putting that variable into the ISABOUT statement, but when I run the SP, the resulting table is empty, so I'm assuming the variable isn't working and I'm not sure where to go from here.

这是我到目前为止所拥有的:

Here's what I have so far:

CREATE PROCEDURE rankingSP (@Topic varchar(30))
AS
BEGIN
    --creates table to display when sp is executed  
    CREATE TABLE #rankingTable(
    Post_ID     int,
    Post_cont   varchar(max),
    [Rank]      decimal(18,2))

    --creates string with keywords and weights
    DECLARE @keywordString varchar(max)
    SELECT @keywordString = COALESCE(@keywordString + ',','') 
    + Keyword + ' ' + 'WEIGHT' + '(' + CONVERT(varchar,K_weight) + ')'
    FROM Keyword
    PRINT @keywordString

    --inserts rankings into rankingTable
    INSERT INTO #rankingTable
    SELECT
    p.[Post_ID],
    p.[Post_cont],
    ct.[RANK]
    FROM CONTAINSTABLE
    (
    Post,
    Post_cont,
    N'ISABOUT (@keywordString)'
    ) ct
    INNER JOIN Post p
    ON ct.[KEY] = p.Post_ID
    ORDER BY ct.[RANK] DESC;

    --displays the ranking table
    SELECT * FROM #rankingTable
    ORDER BY [Rank]DESC
END

推荐答案

在我看来，由于您传递搜索条件的方式，sql引擎无法将其识别为变量，而只是将其识别为字符串.自从我对CONTAINSTABLE做任何事情以来已经有一段时间了，但是我认为如果您这样尝试，它应该可以工作.

It seems to me that because of the way your passing the search condition the sql engine doesn't recognize it as variable but simply a string. It's been awhile since I did anything with CONTAINSTABLE but I think it should work if you try it like this.

--- snippet
FROM CONTAINSTABLE
(
 Post,
 Post_cont,
 N'ISABOUT (' + @keywordString + ')'
)
ct
INNER JOIN Post p
  ON ct.[KEY] = p.Post_ID
  ORDER BY ct.[RANK] DESC;

此外，您可能需要传递"引号.这是一个类似问题，它演示了相同的概念.

Further, you may need to pass the "" quotes. Here is a similar question that demonstrates the same concept.

这篇关于使用ISABOUT时可以使用变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！