.TEXT 0.95笔记八：搜索功能
dinas...@gmail.com
搜索有两种方式，一种为全局模式，也就是在BLOG首页进行，搜索结果可以显示全部BLOG中的内容，可以按作者、内容、标题或全部；另一种是私有模式，即在个人模式下，搜索出的内容与个人有关，当然，他人的回复或回复他人的内容也在其内！
下面是全局搜索的一段存储过程，模糊查询，效率不是太高，与分页结合：
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[blog_SearchAllBySearchText]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[blog_SearchAllBySearchText]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE Procedure blog_SearchAllBySearchText
(
@SearchText nvarchar(100),
@SearchWay int,
@PageIndex int,
@PageSize int
)
as
SET @SearchText = '%' + @SearchText +'%'
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #TempSearchPagedEntryIDs
(
TempID int IDENTITY (1, 1) NOT NULL,
EntryID int NOT NULL
)
if (@SearchWay=0 or @SearchWay=null)
begin
INSERT INTO #TempSearchPagedEntryIDs (EntryID)
SELECT [ID]
FROM blog_Content
WHERE (Title LIKE @SearchText) OR (Text LIKE @SearchText) OR (Author
LIKE @SearchText)
ORDER BY DateAdded DESC
End
if(@SearchWay=1)
begin
INSERT INTO #TempSearchPagedEntryIDs (EntryID)
SELECT [ID]
FROM blog_Content
WHERE (Title LIKE @SearchText)
ORDER BY DateAdded DESC
End
if(@SearchWay=2)
begin
INSERT INTO #TempSearchPagedEntryIDs (EntryID)
SELECT [ID]
FROM blog_Content
WHERE (Text LIKE @SearchText)
ORDER BY DateAdded DESC
End
if(@SearchWay=3)
begin
INSERT INTO #TempSearchPagedEntryIDs (EntryID)
SELECT [ID]
FROM blog_Content
WHERE (Author LIKE @SearchText)
ORDER BY DateAdded DESC
End
SELECT c.Text, ISNULL(c.Description, '') AS Description, c.DateAdded,
c.PostType, c.Title, c.Author, c.FeedBackCount,
ISNULL
((SELECT SUM(e.WebCount + e.AggCount)
FROM blog_EntryViewCount e
WHERE e.BlogID = c.BlogID AND e.EntryID = c.[ID]), 0) AS
ViewCount,bc.Application,bc.host,ISNULL
((SELECT COUNT([EntryID])? FROM #TempSearchPagedEntryIDs), 0) AS
PageTotalRecords
FROM blog_Content c
INNER JOIN Blog_Config bc ON (c.BlogID = bc.BlogID)
INNER JOIN #TempSearchPagedEntryIDs tmp ON (c.[ID] = tmp.EntryID)
WHERE tmp.TempID > @PageLowerBound
AND tmp.TempID < @PageUpperBound
ORDER BY c.DateAdded DESC
DROP TABLE #TempPagedEntryIDs
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO