SqlServer2005分页存储过程的实现

编辑
  • 文档创建者:文档助手1
  • 浏览次数:8355次
  • 编辑次数:17次
  • 最近更新:Kevin-s 于 2019-07-01
  • 1.描述

    直接使用数据集可以实现分页查询,下面我们要做的就是如何定义分页存储过程即直接在存储过程中实现分页查询了。

    2.通用的分页存储过程

    1.1 存储过程

    具体代码如下:

    CREATE proc [dbo].[up_Page2005] 
     @TableName varchar(50),        --表名
     @Fields varchar(5000) = '*',    --字段名(全部字段为*)
     @OrderField varchar(5000),        --排序字段(必须!支持多字段)
     @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
     @pageSize int,                    --每页多少条记录
     @pageIndex int = 1 ,            --指定当前为第几页
     @TotalPage int output            --返回总页数
    as
    begin
        Begin Tran --开始事务
        Declare @sql nvarchar(4000);
        Declare @totalRecord int;    
        --计算总记录数
        if (@SqlWhere='' or @sqlWhere=NULL)
            set @sql = 'select @totalRecord = count(*) from ' + @TableName
        else
            set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
        EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT --计算总记录数       
        --计算总页数
        select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
        if (@SqlWhere='' or @sqlWhere=NULL)
            set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
        else
            set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where ' + @SqlWhere    
            
    
        --处理页数超出范围情况
        if @PageIndex<=0 
            Set @pageIndex = 1
        
        if @pageIndex>@TotalPage
            Set @pageIndex = @TotalPage
    
         --处理开始点和结束点
        Declare @StartRecord int
        Declare @EndRecord int
        
        set @StartRecord = (@pageIndex-1)*@PageSize + 1
        set @EndRecord = @StartRecord + @pageSize - 1
    
        --继续合成sql语句
        set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
         print @sql   
        Exec(@Sql)
        ---------------------------------------------------
        If @@Error <> 0
          Begin
            RollBack Tran
            Return -1
          End
         Else
          Begin
            Commit Tran
            Return @totalRecord ---返回记录总数
          End    
    End

    1.2 测试分页

    点击模板>模板数据集或者直接在数据集面板中,点击+号,添加一个存储过程,切换到 SQLServer 数据库所在的数据连接,双击所需要添加的存储过程,如下图:

    点击预览,需要输入参数,如下图:

    输入参数后,点击确定,返回两个数据集,如下图:

    3.确定 SQL 及每页显示条数的分页存储过程

    3.1 存储过程

    若已经确定了需要分页的表,字段,排序字段及每页显示记录数,可以不定义这几个参数,如下的例子是已知表名为 orders,让其按照 orderno 列进行排序并一页显示 10 条数据:

    具体代码如下:


    3.2 测试分页

    点击模板>模板数据集或者直接在数据集面板中,点击+号,添加一个存储过程,切换到 SQL Server 数据库所在的数据连接,双击所需要添加的存储过程,如下图:

    点击预览,需要输入参数,如下图:

    输入参数后,点击确定,返回两个数据集,如下图:

    注:在调用存储过程时,需要设置下 pageIndex 这个参数的默认值,否则会提示‘每页返回结果集’的错误。

    4.SQL Server2012分页存储过程

    注:上述第二种分页方式,在SQL Server 2012版本以后,有更加简单的实现方式。

    具体实现如下所示:

    4.1 存储过程

    同上述第二种分页方法,已经确定了需要分页的表,字段,排序字段及每页显示记录数,如下的例子是已知表名为 yjcksjb,让其按照 x_jsdh 列进行倒序排列并一页显示 10 条数据:

    具体代码如下:

    create procedure dbo.test5
    @pagesize AS BIGINT = 10,
    @pagenum AS BIGINT = 4
    AS
    BEGIN
    SET nocount ON
    SELECT * 
    FROM yjcksjb 
    ORDER BY x_jsdh DESC 
    OFFSET (@pagenum-1) * @pagesize ROWS 
    FETCH NEXT @pagesize ROWS ONLY
    set nocount off
    end

    4.2 测试分页

    点击模板>模板数据集或者直接在数据集面板中,点击+号,添加一个存储过程,切换到SQL Server 数据库所在的数据连接,双击所需要添加的存储过程,如下图:


    点击预览,需要输入参数,如下图:


    输入参数后,点击确定,返回数据集,如下图:



    附件列表


    主题: 数据连接
    标签: 暂无标签 编辑/添加标签
    如果您认为本文档还有待完善,请编辑

    文档内容仅供参考,如果你需要获取更多帮助,付费/准付费客户请咨询帆软技术支持
    关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
    若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

    此页面有帮助吗?只是浏览 [ 去社区提问 ]