3

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
kelsothenerd
  • 89
  • 1
  • 2
  • 6

1 Answers1

1

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.

Community
  • 1
  • 1
RThomas
  • 10,702
  • 2
  • 48
  • 61
  • Wouldn't putting the weight at the end of the statement with the keywords at the start result in a string of keywords with the weights added on the end? – kelsothenerd Oct 14 '12 at 02:37
  • Yeah, I see what your saying... I wasn't getting what your were doing with the coalesce at first but yes you are correct. Anyway, the theory is still the same with the @keywordString - it cant be enclosed in '' characters otherwise it gets read in as a literal. – RThomas Oct 14 '12 at 06:05
  • I simplified my answer to the core problem... you'll have to figure out how to build the @keywordString, but when you pass it to the CONTAINSTABLE break it out single quotes first. – RThomas Oct 14 '12 at 06:22