分页存储过程

CREATE PROCEDURE dbo . cs_guestbook_GetPosts
(
     @PageIndex   int ,
     @PageSize   int ,
     @SortOrder  bit ,
     @SectionID   int ,
     @SettingsID   int
)
AS
SET Transaction Isolation Level  Read  UNCOMMITTED
BEGIN

DECLARE  @PageLowerBound   int
DECLARE  @PageUpperBound   int
DECLARE  @ThreadID   int

--  First set the rowcount
DECLARE  @RowsToReturn   int
SET  @RowsToReturn   =   @PageSize   *  ( @PageIndex   +   1 )
SET ROWCOUNT  @RowsToReturn

--  Set the page bounds
SET  @PageLowerBound   =   @PageSize   *   @PageIndex
SET  @PageUpperBound   =   @PageLowerBound   +   @PageSize   +   1


--  Create a temp table to store the  select  results
CREATE TABLE  # PageIndex 
(
    IndexID  int  IDENTITY ( 1 ,   1 ) NOT NULL ,
    PostID  int
)

--   Sort  by Post Date
IF    @SortOrder   =   0
    INSERT INTO  # PageIndex (PostID)
     SELECT  PostID FROM cs_Posts (nolock) WHERE IsApproved  =   1  AND SectionID  =   @SectionID  and SettingsID  =   @SettingsID  ORDER BY PostID

ELSE  
    INSERT INTO  # PageIndex (PostID)
     SELECT  PostID FROM cs_Posts (nolock) WHERE IsApproved  =   1  AND SectionID  =   @SectionID  and SettingsID  =   @SettingsID  ORDER BY PostID DESC


--   Select  the individual posts
SELECT
    P . PostID ,  P . ThreadID ,  P . ParentID ,  P . PostAuthor ,  P . UserID ,  P . SectionID ,  P . PostLevel ,  P . SortOrder ,  P . Subject ,  P . PostDate ,  P . IsApproved ,
    P . IsLocked ,  P . IsIndexed ,  P . TotalViews ,  P . Body ,  P . FormattedBody ,  P . IPAddress ,  P . PostType ,  P . EmoticonID ,  P . SettingsID ,  P . AggViews ,
    P . PropertyNames as PostPropertyNames ,  P . PropertyValues as PostPropertyValues ,
    P . PostConfiguration ,
    P . Points as PostPoints ,  P . RatingSum as PostRatingSum ,  P . TotalRatings as PostTotalRatings ,
    T .*,  U .*,   # PageIndex.*,
    T . IsLocked ,
    T . IsSticky ,
    Username  =  P . PostAuthor ,
    EditNotes  =  ( SELECT  EditNotes FROM cs_PostEditNotes WHERE PostID  =  P . PostID) ,
    AttachmentFilename  =   '' ,
    Replies  =   0 ,
    IsModerator  =   0 ,
    HasRead  =   0   --  not used
FROM 
    cs_Posts P (nolock) ,
    cs_Threads T ,
    cs_vw_Users_FullUser U ,
     # PageIndex
WHERE 
    P . PostID  =   # PageIndex.PostID AND
    P . UserID  =  U . cs_UserID AND
    T . ThreadID  =  P . ThreadID AND
     # PageIndex.IndexID > @PageLowerBound AND
    #PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
    IndexID
END


   SELECT  Count(PostID) FROM cs_Posts (nolock) WHERE IsApproved  =   1  AND SectionID  =   @SectionID  and SettingsID  =   @SettingsID

DROP Table  # PageIndex



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo] . [cs_guestbook_GetPosts]  TO [public]
GO

 

网上找到一个简短版

CREATE procedure pagination1
( @pagesize   int ,   -- 页面大小，如每页存储20条记录
@pageindex   int   -- 当前页码
)
as

set nocount on

begin
declare  @indextable  table(id  int  identity( 1 , 1 ) , nid  int )  -- 定义表变量
declare  @PageLowerBound   int   -- 定义此页的底码
declare  @PageUpperBound   int   -- 定义此页的顶码
set  @PageLowerBound = ( @pageindex - 1 ) * @pagesize
set  @PageUpperBound = @PageLowerBound + @pagesize
set rowcount  @PageUpperBound
insert into  @indextable (nid)  select  gid from TGongwen 
where fariqi  > dateadd(day ,- 365 , getdate()) order by fariqi desc
select  O . gid , O . mid , O . title , O . fadanwei , O . fariqi from TGongwen O , @indextable  t 
           where O . gid = t . nid and t . id > @PageLowerBound  
                        and t . id <= @PageUpperBound  order by t . id
end

set nocount off

 

从publish 表中取出第 n 条到第 m 条的记录：
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
(SELECT TOP n-1 id
 FROM publish))
对应的存储过程

CREATE PROCEDURE pagination2
(
@SQL  nVARCHAR( 4000 ) ,   -- 不带排序语句的SQL语句
@Page   int ,   -- 页码
@RecsPerPage   int ,   -- 每页容纳的记录数
@ID  VARCHAR( 255 ) ,   -- 需要排序的不重复的ID号
@Sort  VARCHAR( 255 )  -- 排序字段及规则
)
AS

DECLARE  @Str  nVARCHAR( 4000 )

SET  @Str = '' SELECT  TOP  '' + CAST( @RecsPerPage  AS VARCHAR( 20 )) + ''   *  FROM 
( '' + @SQL + '' ) T WHERE T . '' + @ID + '' NOT IN ( SELECT  TOP  '' + CAST(( @RecsPerPage * ( @Page - 1 )) 
AS VARCHAR( 20 )) + ''   '' + @ID + ''  FROM ( '' + @SQL + '' ) T9 ORDER BY  '' + @Sort + '' ) ORDER BY  '' + @Sort

PRINT   @Str

EXEC  sp_ExecuteSql  @Str
GO

TOP可以提高我们的查询速度，而NOT IN会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就要彻底改造NOT IN，同其他方法来替代它:
select top 页大小 *
from table1
where id>
(select max (id) from
(select top ((页码-1)*页大小) id from table1 order by id) as T
)
order by id

CREATE PROCEDURE pagination3
@tblName  varchar( 255 ) ,   --  表名
@strGetFields  varchar( 1000 )  =   '' * '' ,   --  需要返回的列 
@fldName  varchar( 255 ) = '''' ,   --  排序的字段名
@PageSize   int   =   10 ,   --  页尺寸
@PageIndex   int   =   1 ,   --  页码
@doCount  bit  =   0 ,   --  返回记录总数 ,  非  0  值则返回
@OrderType  bit  =   0 ,   --  设置排序类型 ,  非  0  值则降序
@strWhere  varchar( 1500 )  =   ''''   --  查询条件 (注意 :  不要加 where)
AS

declare  @strSQL  varchar( 5000 )  --  主语句
declare  @strTmp  varchar( 110 )  --  临时变量
declare  @strOrder  varchar( 400 )  --  排序类型

if   @doCount   !=   0
begin
if   @strWhere   != ''''
set  @strSQL   =   " select count(*) as Total from [ "   +   @tblName   +   " ] where  " + @strWhere
else
set  @strSQL   =   " select count(*) as Total from [ "   +   @tblName   +   " ] "
end 
以上代码的意思是如果 @doCount传递过来的不是0 ，就执行总数统计。以下的所有代码都是 @doCount为0的情况 ：

else
begin
if   @OrderType   !=   0
begin
set  @strTmp   =   " <(select min "
set  @strOrder   =   "  order by [ "   +   @fldName   + " ] desc "
如果 @OrderType不是0 ，就执行降序，这句很重要！

end
else
begin
set  @strTmp   =   " >(select max "
set  @strOrder   =   "  order by [ "   +   @fldName   + " ] asc "
end

if   @PageIndex   =   1
begin
if   @strWhere   !=   ''''  

set  @strSQL   =   " select top  "   +  str( @PageSize )  + "   " + @strGetFields +   "
from [ "   +   @tblName   +   " ] where  "   +   @strWhere   +   "   "   +   @strOrder
else

set  @strSQL   =   " select top  "   +  str( @PageSize )  + "   " + @strGetFields +   "  
from [ " +   @tblName   +   " ]  " +   @strOrder
如果是第一页就执行以上代码，这样会加快执行速度

end
else
begin
以下代码赋予了 @strSQL以真正执行的SQL代码 　

set  @strSQL   =   " select top  "   +  str( @PageSize )  + "   " + @strGetFields +   "  from [ "
+   @tblName   +   " ] where [ "   +   @fldName   +   " ] "   +   @strTmp   +   " ([ " +   @fldName   +   " ]) 
from (select top  "   +  str(( @PageIndex - 1 ) * @PageSize )  +   "  [ " +   @fldName   +   " ] 
from [ "   +   @tblName   +   " ] "   +   @strOrder   +   " ) as tblTmp) " +   @strOrder

if   @strWhere   !=   ''''
set  @strSQL   =   " select top  "   +  str( @PageSize )  + "   " + @strGetFields +   "  from [ "
+   @tblName   +   " ] where [ "   +   @fldName   +   " ] "   +   @strTmp   +   " ([ "
+   @fldName   +   " ]) from (select top  "   +  str(( @PageIndex - 1 ) * @PageSize )  +   "  [ "
+   @fldName   +   " ] from [ "   +   @tblName   +   " ] where  "   +   @strWhere   +   "   "
+   @strOrder   +   " ) as tblTmp) and  "   +   @strWhere   +   "   "   +   @strOrder
end 

end 

exec  ( @strSQL )

GO

 

原文:http://www.51r.com/user3/kgoo/archives/2006/91046.shtml

 

继续增加范例

CREATE   PROCEDURE  dbo.tbh_Articles_GetArticles
(
    @PageIndex   int ,
    @PageSize   int
)
AS
SET  NOCOUNT  ON

--  create a temporary table
CREATE   TABLE  #TempArticles
(
   ID  int   IDENTITY ( 1 , 1 ),
   ArticleID  int

)

--  populate the temporary table
INSERT   INTO  #TempArticles (ArticleID)
    SELECT  ArticleID  FROM  tbh_Articles  ORDER   BY  ReleaseDate  DESC

--  get a page of records from the temporary table,
--  and join them with the real table
SELECT  ID, tbh_Articles. *
    FROM  #TempArticles  INNER   JOIN  tbh_Articles
       ON  tbh_Articles.ArticleID  =  #TempArticles.ArticleID
    WHERE  ID  BETWEEN
      ( @PageIndex * @PageSize + 1 )  AND  (( @PageIndex + 1 ) * @PageSize )

 

FOR SQL2005 

CREATE   PROCEDURE  dbo.tbh_Articles_GetArticles
(
    @PageIndex    int ,
    @PageSize     int
)
AS
SET  NOCOUNT  ON

SELECT   *   FROM
(
    SELECT  tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy,
      tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract,
      tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State,
      tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate,
      tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled,
      tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes,
      tbh_Articles.TotalRating, tbh_Categories.Title  AS  CategoryTitle,
      ROW_NUMBER()  OVER  ( ORDER   BY  ReleaseDate  DESC )  AS  RowNum
       FROM  tbh_Articles  INNER   JOIN
         tbh_Categories  ON  tbh_Articles.CategoryID  =  tbh_Categories.CategoryID
) Articles
    WHERE  Articles.RowNum  BETWEEN
      ( @PageIndex * @PageSize + 1 )  AND  (( @PageIndex + 1 ) * @PageSize )
    ORDER   BY  ReleaseDate  DESC