幾種分頁存儲過程

過程一：

select top N條記錄 * from 文章表 where id not in(select top M條記錄 id from 文章表 order by id desc ) order by id desc

過程二：

select top N條記錄 * from 文章表 where id <(select min(id) from (select top M條記錄 id from 文章表 order by id desc ) as tblTmp) order by id desc

--簡單通用

CREATE PROCEDURE [dbo].[PageView]

    @select VARCHAR(max),

    @CurrentPage INT,

    @PageSize INT

AS

BEGIN

    declare @sql NVARCHAR(max)

    DECLARE @RecordCurrent INT

    DECLARE @PageCount INT

    DECLARE @RecordCount INT

    SET NOCOUNT ON

    set @sql='select @RecordCount=count(*) from ('+@select+') a'

    exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output

    SET @PageCount=(@RecordCount+@PageSize-1)/@PageSize

    IF ISNULL(@CurrentPage,0)<1

        SET @CurrentPage=1

    ELSE if ISNULL(@CurrentPage,0)>@PageCount

        SET @CurrentPage=@PageCount

    SELECT @CurrentPage AS CurrentPage,@RecordCount AS RecordCount,@PageSize AS PageSize,@PageCount AS PageCount

    set @sql='select * from ('+@select+') a where rownumber between '+cast((@CurrentPage-1)*@PageSize+1 as varchar)+' and '+cast(@CurrentPage*@PageSize as varchar)

    exec (@sql)

END

--使用not in 方式的存儲過程

create PROCEDURE GetPageDataByNotIn

@PageIndex int,/**//*當前頁數(shù)*/ 

@PageSize int/**//*每頁大小*/ 

AS

declare @starttime datetime

    set @starttime=getdate()

IF @PageIndex > 0

BEGIN

set nocount on;

DECLARE @PageLowerBound int

DECLARE @StartID int

DECLARE @sql varchar(225)

SET @PageLowerBound = @PageSize * (@PageIndex-1)

IF @PageLowerBound<1

   SET @PageLowerBound=1

    print @PageLowerBound

select top (@PageSize) * from table where [ar_id] not in (select top ((@PageSize)*(@PageIndex-1)) ar_id from table )

EXEC(@sql)

set nocount off; 

END

print '耗時='+convert(varchar(30),datediff(ms,@starttime,getdate()))

-------------------------------------------------------------------------------------------------------

--使用ROWCOUNT的分頁存儲過程

create PROCEDURE GetPageData

@PageIndex int,/**//*當前頁數(shù)*/ 

@PageSize int/**//*每頁大小*/ 

AS

declare @starttime datetime

    set @starttime=getdate()

IF @PageIndex > 0

BEGIN

set nocount on;

DECLARE @PageLowerBound int

DECLARE @StartID int

DECLARE @sql varchar(225)

SET @BeginIndex = @PageSize * (@PageIndex-1)

IF @BeginIndex<1

   SET @BeginIndex=1

SET ROWCOUNT @BeginIndex

SELECT @StartID = [ar_id] FROM table ORDER BY ar_id 

print @StartID

SET ROWCOUNT 0

SET @sql='select top '+str(@PageSize) +' * from table where [ar_id]>='+ str(@StartID) +' ORDER BY [ar_id] '

EXEC(@sql)

set nocount off; 

END

print '耗時='+convert(varchar(30),datediff(ms,@starttime,getdate()))

-----------------------------------------------------------------------------------------------------

測試查詢一張有100W條數(shù)據(jù)的表table，每頁顯示10條數(shù)據(jù)

存儲過程                        第1頁   第10頁   第100頁   第1000頁   第5000頁   

GetPageDataByNotIn    0           0            126           13530       等了2分多鐘沒耐性了....

GetPageData                0           0            0               16              76

------------------------------------------------------------------------------------------------------------

最后使用的存儲過程(推薦)：

create PROCEDURE GetPageData

(

@TableName varchar(30),--表名稱

@IDName varchar(20),--表主鍵名稱

@PageIndex int,--當前頁數(shù) 

@PageSize int--每頁大小 

)

AS

IF @PageIndex > 0

BEGIN

set nocount on

   DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)

   SET @PageLowerBound = @PageSize * (@PageIndex-1)

   IF @PageLowerBound<1

    SET @PageLowerBound=1

   SET ROWCOUNT @PageLowerBound

   SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName

     exec sp_executesql @sql,N'@StartID int output',@StartID output

   SET ROWCOUNT 0

   SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '

   EXEC(@sql)

set nocount off

END