[分享加散分]sql server 2005分页存储过程和sql server 2000分页存储过程

周公
博客专家认证
2008-11-06 10:04:52
sql server 2005分页存储过程和sql server 2000分页存储过程,sql 2005的分页存储过程分3个版本,一个是没有优化过的,一个是优化过的,最后一个支持jion的,sql2000的分页存储过程,也可以运行在sql2005上,但是性能没有sql2005的版本好。
sql 2005:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

sql 2005:

/****** 对象: StoredProcedure [dbo].[up_Page2005V2] 脚本日期: 05/21/2008 11:27:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_Page2005V2]
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@totalRecord int = 0,
@TotalPage int output --返回总页数
AS
BEGIN

Begin Tran --开始事务

Declare @sql nvarchar(4000);

if @totalRecord<=0 begin
--计算总记录数

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--计算总记录数
end

--计算总页数
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


GO
...全文
5045 57 打赏 收藏 转发到动态 举报
写回复
用AI写文章
57 条回复
切换为时间正序
请发表友善的回复…
发表回复
haorenhl007 2012-07-08
  • 打赏
  • 举报
回复
多谢周公指点
Terrancy 2012-05-11
  • 打赏
  • 举报
回复
其实我想说的是无图无真相,应该把实验的分页的结果都贴上来的,这样人家才能心服口服的。更重要的是结果才是王道。。
yannyleeqr 2011-12-27
  • 打赏
  • 举报
回复
刚接触存储过程,不是太懂。不过学习
aboutrhino 2011-11-23
  • 打赏
  • 举报
回复
sql 2000的有问题:
当Order by 中不是用ID排序,
就会有问题了。
小狼15454 2011-10-27
  • 打赏
  • 举报
回复
学习吧 可还是很乱 能不能 写个 通用的
kakajay008 2011-10-07
  • 打赏
  • 举报
回复
每天回帖即可获得10分可用分每天回帖即可获得10分可用分每天回帖即可获得10分可用分
bbbrid 2011-07-05
  • 打赏
  • 举报
回复
居然不支持DISTINCT
bbbrid 2011-06-01
  • 打赏
  • 举报
回复
第3个存储过程真是垃圾,使用过程中出现的问题太多了,使用复杂的where查询条件时,居然不可以使用分号,对SQL注入也没做任何处理,我最后不得不返回来 select count(Id), 再 select *,哎
sxhus 2011-05-30
  • 打赏
  • 举报
回复
学习一下
bbbrid 2011-05-27
  • 打赏
  • 举报
回复
UP,UP
bbbrid 2011-05-27
  • 打赏
  • 举报
回复


public IList<ProductInfo> GetProductsByCategoryId(int CategoryId,ref PageInfo pgInfo)
{
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@CategoryId", CategoryId);
param[1] = new SqlParameter("@PageIndex", pgInfo.PageIndex);
param[2] = new SqlParameter("@PageSize", pgInfo.PageSize);
param[3] = new SqlParameter("@PageCount", pgInfo.PageCount);
param[3].Direction = ParameterDirection.Output;
string sqlCmd = "EXEC GetPage 'MyShop_Products','ProductId,ImgUrl,ProductName,MarketPrice,SalePrice','ProductId DESC',CategoryId=@CategoryId ,@PageSize,@PageIndex,@PageCount OUTPUT";
IList<ProductInfo> ProductList = new List<ProductInfo>();
DataTable Table = SQLHelper.ExecuteTable(CommandType.Text, sqlCmd, param);
pgInfo.PageCount = Convert.ToInt32(param[3].Value); //将总页数赋给PageInfo
foreach (DataRow Row in Table.Rows)
{
ProductInfo newPro = new ProductInfo();
newPro.ProductId = Convert.ToInt32(Row[1]);
newPro.ImgUrl = Convert.ToString(Row[2]);
if (newPro.ImgUrl == "")
{
newPro.ImgUrl = "/images/nopic.jpg";
}
newPro.ProductName = Convert.ToString(Row[3]);
newPro.MarketPrice = Convert.ToDecimal(Row[4]);
newPro.SalePrice = Convert.ToDecimal(Row[5]);
ProductList.Add(newPro);
}
return ProductList;
}

这是我的代码,运行时提示'@CategoryId' 附近有语法错误。该怎么解决呀?
bbbrid 2011-05-27
  • 打赏
  • 举报
回复
up_Page2005V2_Join,
这个函数在C#怎么调用呢?
比如有一个Products表,里面有ProductId,ProductName,CategoryId
现在要按照CategoryId来查询里面所有符合条件的记录
要是不带WHERE查询条件的,可以用EXEC up_Page2005V2_Join 'Products','*','ProductId','',@PageSize,@PageIndex,@PageCount OUTPUT
但是现在查询要用到条件,使用
EXEC up_Page2005V2_Join 'Products','*','ProductId','CategoryId=@CategoryId',@PageSize,@PageIndex,@PageCount OUTPUT
却不能运行,提示'@CategoryId' 附近有语法错误。请问该如何解决?
张林_Coder 2011-05-10
  • 打赏
  • 举报
回复
学习了
xylhx 2011-04-20
  • 打赏
  • 举报
回复
学习
a313976010 2011-03-31
  • 打赏
  • 举报
回复
太有才了
VCACC 2010-12-17
  • 打赏
  • 举报
回复
Mark
苏州牛恋歌 2010-12-08
  • 打赏
  • 举报
回复
留个加印
苏州牛恋歌 2010-12-08
  • 打赏
  • 举报
回复
我顶我顶
元元0123 2010-11-26
  • 打赏
  • 举报
回复
学习、、、
狼王_ 2010-10-13
  • 打赏
  • 举报
回复

每天回帖即可获得10分可用分!小技巧:教您如何更快获得可用分
加载更多回复(37)
本课程详细讲解了以下内容:    1.jsp环境搭建及入门、虚拟路径和虚拟主机、JSP执行流程    2.使用Eclipse快速开发JSP、编码问题、JSP页面元素以及request对象、使用request对象实现注册示例    3.请求方式的编码问题、response、请求转发和重定向、cookie、session执行机制、session共享问题     4.session与cookie问题及application、cookie补充说明及四种范围对象作用域     5.JDBC原理及使用Statement访问数据库、使用JDBC切换数据库以及PreparedStatement的使用、Statement与PreparedStatement的区别     6.JDBC调用存储过程和存储函数、JDBC处理大文本CLOB及二进制BLOB类型数据     7.JSP访问数据库、JavaBean(封装数据和封装业务逻辑)     8.MVC模式与Servlet执行流程、Servlet25与Servlet30的使用、ServletAPI详解与源码分析     9.MVC案例、三层架构详解、乱码问题以及三层代码流程解析、完善Service和Dao、完善View、优化用户体验、优化三层(加入接口和DBUtil)    1 0.Web调试及bug修复、分页SQL(Oracle、MySQLSQLSERVER)     11.分页业务逻辑层和数据访问层Service、Dao、分页表示层Jsp、Servlet     12.文件上传及注意问题、控制文件上传类型和大小、下载、各浏览器下载乱码问题     13.EL表达式语法、点操作符和中括号操作符、EL运算、隐式对象、JSTL基础及set、out、remove     14.过滤器、过滤器通配符、过滤器链、监听器     15.session绑定解绑、钝化活化     16.以及Ajax的各种应用     17. Idea环境下的Java Web开发
MyBatis-Plus(简称 MP)是一个 MyBatis 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。 愿景我们的愿景是成为 MyBatis 超好的搭档,就像 魂斗罗 中的 1P、2P,基友搭配,效率翻倍。 特性无侵入:只做增强不做改变,引入它不会对现有工程产生影响,如丝般顺滑损耗小:启动即会自动注入基本 CURD,性能基本无损耗,直接面向对象操作强大的 CRUD 操作:内置通用 Mapper、通用 Service,仅仅通过少量配置即可实现单表大部分 CRUD 操作,更有强大的条件构造器,满足各类使用需求支持 Lambda 形式调用:通过 Lambda 表达式,方便的编写各类查询条件,无需再担心字段写错支持主键自动生成:支持多达 4 种主键策略(内含分布式唯一 ID 生成器 - Sequence),可自由配置,完美解决主键问题支持 ActiveRecord 模式:支持 ActiveRecord 形式调用,实体类只需继承 Model 类即可进行强大的 CRUD 操作支持自定义全局通用操作:支持全局通用方法注入( Write once, use anywhere )内置代码生成器:采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码,支持模板引擎,更有超多自定义配置等您来使用内置分页插件:基于 MyBatis 物理分页,开发者无需关心具体操作,配置好插件之后,写分页等同于普通 List 查询分页插件支持多种数据库:支持 MySQL、MariaDB、Oracle、DB2、H2、HSQLSQLite、Postgre、SQLServer 等多种数据库内置性能分析插件:可输出 Sql 语句以及其执行时间,建议开发测试时启用该功能,能快速揪出慢查询内置全局拦截插件:提供全表 delete 、 update 操作智能分析阻断,也可自定义拦截规则,预防误操作 我们将通过理论与实操的方式来阐述 MyBatis-Plus 的强大功能,体验和学习MyBatis-Plus技术。

27,580

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧