SqlServer2005分页存储过程的实现

编辑
文档创建者:文档助手1 (67727 )     浏览次数:3485次     编辑次数:14次     最近更新:jiangsr 于 2018-01-23     

目录:

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数据库所在的数据连接,双击所需要添加的存储过程,如下图:
点击预览,需要输入参数,如下图:
输入参数后,点击确定,返回两个数据集,如下图:

2.确定sql及每页显示条数的分页存储过程编辑

2.1 存储过程
若已经确定了需要分页的表,字段,排序字段及每页显示记录数,可以不定义这几个参数,如下的例子是已知表名为orders,让其按照orderno列进行排序并一页显示10条数据:
具体代码如下:
Create proc [dbo].[test1] @pageIndex int = 1 , --指定当前为第几页 @TotalPage int output --返回总页数 as begin Begin Tran --开始事务 Declare @sql nvarchar(4000); Declare @totalRecord int; --计算总记录数 set @sql = 'select @totalRecord = count(*) from orders' EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数 select @TotalPage=CEILING((@totalRecord+0.0)/10) set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by orderno) as rowId,* from orders' --处理页数超出范围情况 if @PageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --处理开始点和结束点 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1)*10 + 1 set @EndRecord = @StartRecord + 10 - 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 
2.2 测试分页
点击模板>模板数据集或者直接在数据集面板中,点击+号,添加一个存储过程,切换到Sql Server数据库所在的数据连接,双击所需要添加的存储过程,如下图:
点击预览,需要输入参数,如下图:
输入参数后,点击确定,返回两个数据集,如下图:

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

3.Sql Server2012分页存储过程编辑

注:上述第二种分页方式,在sql server 2012版本以后,有更加简单的实现方式。
具体实现如下所示:
3.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
3.2 测试分页
点击模板>模板数据集或者直接在数据集面板中,点击+号,添加一个存储过程,切换到Sql Server数据库所在的数据连接,双击所需要添加的存储过程,如下图:
222
点击预览,需要输入参数,如下图:
222
输入参数后,点击确定,返回数据集,如下图:
222

附件列表


主题: 设计思路
如果您认为本文档还有待完善,请编辑

文档内容仅供参考,如果你需要获取更多帮助,请咨询帆软技术支持
关于技术问题,您还可以通过帆软论坛获取帮助,论坛上有非常多的大神,有些水平比帆软工程师还要高哦。
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

本文档是否有用?
谢谢! 我们非常感谢您的反馈。
提交反馈: