SqlServer内部的分页功能

--利用SQL未公开的存储过程实现分页    

    if exists (select * from dbo.sysobjects    
      where id = object_id(N'[dbo].[p_splitpage]')    
      and OBJECTPROPERTY(id,N'IsProcedure') = 1)    
    drop procedure [dbo].[p_splitpage]    
    GO    
     
    create procedure p_splitpage        
    @sql nvarchar(4000),--要执行的sql语句    
    @currentpage int=2,--要显示的页码    
    @pagesize int=10,--每页的大小    
    @recordcount int=0 out,--记录数    
    @pagecount int=0 out --总页数    
    as    
    set nocount on    
    declare @p1 int    
     
    exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output    
     
    select @recordcount=@pagecount,@pagecount=ceiling(1.0*@pagecount/@pagesize),@currentpage=(@currentpage-1)*@pagesize+1        
    select @recordcount recordcount,@pagecount     pagecount,@currentpage     currentpage    
    exec sp_cursorfetch @p1,16,@currentpage,@pagesize        
    exec sp_cursorclose @p1    
    go

CREATE PROC spGetPages2 @iRowCount INT,@iPageNo INT
AS
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID ASC) RowNum,* FROM Production.Product ) OrderData
WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 AND @iRowCount*@iPageNo
ORDER BY ProductID ASC
GO

EXEC spGetPages2 10,20

--//调用的方式
表
exec up_zbh_DivPageBySql 'select * from 表',10,3
存储过程
exec up_zbh_DivPageBySql 'exec 存储过程',1

--//我把它封装成一个存储过程,调用的时候方便的很哈!!
create procedure up_zbh_DivPageBySql
?@strSql varchar(8000),
?@nPageSize int,
?@nPageCount int
as
??? SET NOCOUNT ON?
??? DECLARE @P1 INT,
??? @nRowCount INT

??? --//注意:@scrollopt = 1 会取得Select的时候的总行数
??? EXEC sp_cursoropen @P1 OUTPUT,@strSql,@scrollopt = 2,@ccopt = 335873,@rowcount = @nRowCount OUTPUT

??? IF (@P1 != 0)
??? BEGIN
--SELECT @nRowCount AS nRecordCount,ceiling(1.0 * @nRowCount / @nPageSize) AS nPageCount,@nPageCount AS nPage
SET @nPageCount = (@nPageCount - 1) * @nPageSize + 1?
EXEC sp_cursorfetch @P1,32,@nPageCount,@nPageSize??
EXEC sp_cursorclose @P1
??? END

GO

--//调用的方式表exec up_zbh_DivPageBySql 'select * from ptype',4存储过程exec up_zbh_DivPageBySql 'exec 存储过程',1

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!