分页存储过程

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