CREATE PROCEDURE T_Rank
@T_name VARCHAR(35)
AS
BEGIN
DECLARE @K VARCHAR(35),@W VARCHAR(5),@String VARCHAR(255)
DECLARE temptable CURSOR STATIC
FOR SELECT Keyword,Weight FROM Keyword WHERE Topic_name=@T_name
OPEN temptable
FETCH NEXT FROM temptable INTO @K,@W
SET @String=''
--Loop until no more row appear
WHILE ( @@FETCH_STATUS <>-1)
BEGIN
SET @String=@String+','+@K+' WEIGHT('+@W+')'
FETCH NEXT FROM temptable INTO @K,@W
END
CLOSE temptable
DEALLOCATE temptable
SET @String=(RIGHT(@String,len(@String)-1))
SET @String ='''ISABOUT('+@String +')''' SELECT FT_TBL.ID, FT_TBL.Txt, KEY_TBL.Rank
FROM Post AS FT_TBL INNER JOIN CONTAINSTABLE ( Post, Txt, @String) AS KEY_TBL
ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.Rank DESC
END帮我改改这个储存过程,一直报错,谢谢了!!
@T_name VARCHAR(35)
AS
BEGIN
DECLARE @K VARCHAR(35),@W VARCHAR(5),@String VARCHAR(255)
DECLARE temptable CURSOR STATIC
FOR SELECT Keyword,Weight FROM Keyword WHERE Topic_name=@T_name
OPEN temptable
FETCH NEXT FROM temptable INTO @K,@W
SET @String=''
--Loop until no more row appear
WHILE ( @@FETCH_STATUS <>-1)
BEGIN
SET @String=@String+','+@K+' WEIGHT('+@W+')'
FETCH NEXT FROM temptable INTO @K,@W
END
CLOSE temptable
DEALLOCATE temptable
SET @String=(RIGHT(@String,len(@String)-1))
SET @String ='''ISABOUT('+@String +')''' SELECT FT_TBL.ID, FT_TBL.Txt, KEY_TBL.Rank
FROM Post AS FT_TBL INNER JOIN CONTAINSTABLE ( Post, Txt, @String) AS KEY_TBL
ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.Rank DESC
END帮我改改这个储存过程,一直报错,谢谢了!!
@T_name VARCHAR(35)
AS
BEGIN
DECLARE @K VARCHAR(35),@W VARCHAR(5),@String VARCHAR(255)
DECLARE temptable CURSOR STATIC
FOR SELECT Keyword,Weight FROM Keyword WHERE Topic_name=@T_name
OPEN temptable
FETCH NEXT FROM temptable INTO @K,@W
SET @String=''
--Loop until no more row appear
WHILE ( @@FETCH_STATUS <>-1)--一般使用@@FETCH_STATUS=0,因为还有-2的状态,所以<>-1不够精确
BEGIN
SET @String=@String+','+@K+' WEIGHT('+@W+')'
FETCH NEXT FROM temptable INTO @K,@W
END
CLOSE temptable
DEALLOCATE temptable
SET @String=(RIGHT(@String,len(@String)-1))
SET @String ='''ISABOUT('+@String +')'''--ISABOUT是自定义函数?还是你的是别的语言?
SELECT FT_TBL.ID, FT_TBL.Txt, KEY_TBL.Rank
FROM Post AS FT_TBL INNER JOIN CONTAINSTABLE ( Post, Txt, @String) AS KEY_TBL
ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.Rank DESC
END
Msg 7630, Level 15, State 2, Procedure T_Rank, Line 23
Syntax error near '(' in the full-text search condition ''ISABOUT(A WEIGHT(0.5),B WEIGHT(0.5))''主要是ISABOUT 应该怎么处理呢,我是把ISABOUT整个写成一个字符串了
简单算一下,如果你有大于等于6条以上数据就会报错,@String字段被截取啥的。你把@String 定义成 VARCHAR(max)试试。 其他都没看出什么问题。
ISABOUT就是全文索引里面用的那个不是自己写的题目要求是有两张表,表一Keyword(Topic_name,keyword,weight),表二post(ID,txt)。txt是对topic的讨论已经对post表的txt建立了索引,现在要求,用Topic_name去搜索,返回一个ranking table(ID,Txt,Rank)请问应该怎么样使用keyword 和weight?
有一个简单例子是
CREATE PROCEDURE B_Rank
@T_name VARCHAR(35)
AS
BEGIN
SELECT FT_TBL.ID, FT_TBL.Txt, KEY_TBL.Rank
FROM Post AS FT_TBL INNER JOIN CONTAINSTABLE ( Post, Txt, 'ISABOUT(happy WEIGHT(1.0),glad WEIGHT(0.3))') AS KEY_TBL
ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.Rank DESC
END但是这里的的keyword 和 weight 不是固定的,那怎么把Keyword表里面的keyword 和 weight 用到ISABoUT里面
就是全文索引来着,定义VARCHAR(MAX),也是报错,连储存过程都建立不起来
Msg 4110, Level 16, State 6, Procedure T_Rank, Line 23
The argument type "varchar(max)" is invalid for argument 3 of "CONTAINSTABLE".