存储过程优化(全文检索)

[hq2008]

以下是存储过程:主要是这一句
'SELECT @pages=COUNT(ArticleID) FROM Article where 1=1 and '+'CONTAINS(Title,''"'+ltrim(rtrim(@SearchTitle))+'*"'') or CONTAINS(Content,''"'+ltrim(rtrim(@SearchContent))+'*"'')'

CREATE PROC News_Page
@pagesize int,
@CurPage int,
@SearchTitle varchar(200),
@SearchContent varchar(200),
@Tag int=0,
@pages int output
AS
DECLARE @sql nvarchar(4000)
if(@Tag=1)
begin
if(ltrim(rtrim(@SearchTitle))<>'' and ltrim(rtrim(@SearchContent))<>'')
begin
SET @sql='SELECT @pages=COUNT(ArticleID) FROM Article where 1=1 and '+'CONTAINS(Title,''"'+ltrim(rtrim(@SearchTitle))+'*"'') or CONTAINS(Content,''"'+ltrim(rtrim(@SearchContent))+'*"'')'
end
else
begin
SET @sql='SELECT @pages=COUNT(ArticleID) FROM Article'
end
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数

end

DECLARE @sqlText AS varchar(1000)
DECLARE @sqlTable AS varchar(1000)
if(ltrim(rtrim(@SearchTitle))<>'' and ltrim(rtrim(@SearchContent))<>'')
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@CurPage * @pagesize) AS varchar(30)) + ' ' + ' ArticleID,Title,CopyFrom,IncludePic,DefaultPicUrl,Content,Convert(varchar(20),UpdateTime,111) AS UpdateTime ' +' from article where CONTAINS(Title,''"'+ltrim(rtrim(@SearchTitle))+'*"'') or CONTAINS(Content,''"'+ltrim(rtrim(@SearchContent))+'*"'') order by UpdateTime desc'
end
else
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@CurPage * @pagesize) AS varchar(30)) + ' ' + ' ArticleID,Title,CopyFrom,IncludePic,DefaultPicUrl,Content,Convert(varchar(20),UpdateTime,111) AS UpdateTime from article order by UpdateTime desc '
end

SET @sqlText =
'SELECT TOP ' + CAST(@pagesize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ArticleID NOT IN(SELECT TOP ' +
CAST((@CurPage-1) * @pagesize AS varchar(30)) + ' ' +
'ArticleID FROM (' + @sqlTable + ') AS tableB)'
EXEC (@sqlText)



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO