.Text分页技术(1)分页的存储过程分析

CREATE      PROC blog_GetPageableEntriesByCategoryID
(
 @BlogID int,
 @CategoryID int,
 @PageIndex int,
 @PageSize int,
 @PostType int,
 @SortDesc bit
)
AS

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

--1.由@PageIndex和@PageSize算出临时表中TempID边界
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- ? Only Posts ?

CREATE TABLE #TempPagedEntryIDs
(
 TempID int IDENTITY (1, 1) NOT NULL,
 EntryID int NOT NULL
) 

--2.选出符合@PostType与@CategoryID的BlogID，放入临时表，并根据@SortDesc排序
IF NOT (@SortDesc = 1)
BEGIN
 INSERT INTO #TempPagedEntryIDs (EntryID)
 SELECT blog.[ID]
 FROM  blog_Content blog
   INNER JOIN blog_Links links ON (blog.[ID] = links.PostID)
   INNER JOIN blog_LinkCategories cats ON (links.CategoryID =

cats.CategoryID)
 WHERE  blog.blogID = @BlogID
   AND blog.PostType = @PostType
   AND cats.CategoryID = @CategoryID
 ORDER BY blog.[ID]
END
ELSE
BEGIN
 INSERT INTO #TempPagedEntryIDs (EntryID)
 SELECT blog.[ID]
 FROM  blog_Content blog
   INNER JOIN blog_Links links ON (blog.[ID] = links.PostID)
   INNER JOIN blog_LinkCategories cats ON (links.CategoryID =

cats.CategoryID)
 WHERE  blog.blogID = @BlogID
   AND blog.PostType = @PostType
   AND cats.CategoryID = @CategoryID
 ORDER BY blog.[ID] DESC       --注意该行
END
 
--3.根据边界@PageLowerBound与@PageUpperBound选择返回的记录
SELECT content.BlogID,
  content.[ID],
  content.Title,
  content.DateAdded,
  content.[Text],
  content.[Description],
  content.SourceUrl,
  content.PostType,
  content.Author,
  content.Email,
  content.SourceName,
  content.DateUpdated,
  content.TitleUrl,
  content.FeedbackCount,
  content.ParentID,
  content.PostConfig,
  content.EntryName,
  vc.WebCount,
  vc.AggCount,
  vc.WebLastUpdated,
  vc.AggLastUpdated

FROM blog_Content content
     INNER JOIN #TempPagedEntryIDs tmp ON (content.[ID] = tmp.EntryID)
 Left JOIN  blog_EntryViewCount vc ON (content.[ID] = vc.EntryID and vc.BlogID = @BlogID)
WHERE content.BlogID = @BlogID AND
  tmp.TempID > @PageLowerBound AND
  tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID

--4.删除表
DROP TABLE #TempPagedEntryIDs

--5.返回符合条件的总记录数(个人觉得返回删除的临时表的总数也是不错的)
SELECT  COUNT(blog.[ID]) as TotalRecords
FROM  blog_Content blog
  INNER JOIN blog_Links links ON (blog.[ID] = links.PostID)
  INNER JOIN blog_LinkCategories cats ON (links.CategoryID = cats.CategoryID)
WHERE  blog.blogID = @BlogID
  AND blog.PostType = @PostType
  AND cats.CategoryID = @CategoryID
GO