[分享加散分]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
...全文
4676 57 打赏 收藏 举报
写回复
57 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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分可用分!小技巧:教您如何更快获得可用分
  • 打赏
  • 举报
回复
加载更多回复
相关推荐
发帖
应用实例
加入

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2008-11-06 10:04
社区公告
暂无公告