sql server 2005分页存储过程和sql server 2000分页存储过程，sql 2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持jion的，sql2000的分页存储过程，也可以运行在sql2005上，但是性能没有sql2005的版本好。
USE [svnhost]
GO
/****** 对象: StoredProcedure [dbo].[up_Page2005] 脚本日期: 05/21/2008 11:27:05 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
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
GO
/****** 对象: StoredProcedure [dbo].[up_Page2005V2] 脚本日期: 05/21/2008 11:27:15 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
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
/****** 对象: StoredProcedure [dbo].[up_Page2005V2_Join] 脚本日期: 05/21/2008 11:27:30 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[up_Page2005V2_Join]
@TableName varchar(150), --表名
@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 + ' 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 + ' 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
USE [ game ]
GO
/* ***** 对象: StoredProcedure [dbo].[page] 脚本日期: 05/21/2008 11:37:12 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [ dbo ] . [ page ]
@RecordCount int output,
@ReturnCount bit ,
@QueryStr nvarchar ( 1000 ) = ' table1 ' , -- 表名、视图名、查询语句
@PageSize int = 20 , -- 每页的大小(行数)
@PageCurrent int = 2 , -- 要显示的页 从0开始
@FdShow nvarchar ( 2000 ) = ' * ' , -- 要显示的字段列表
@IdentityStr nvarchar ( 100 ) = ' id ' , -- 主键
@WhereStr nvarchar ( 2000 ) = ' 1=1 ' ,
@FdOrder nvarchar ( 100 ) = ' desc ' -- 排序 只能取desc或者asc
as
set nocount on
declare
@sql nvarchar ( 2000 )
if @WhereStr = '' begin
set @WhereStr = ' 1=1 '
end
if @ReturnCount = 1 begin
declare @tsql nvarchar ( 200 )
set @tsql = N ' select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr
exec sp_executesql @tsql ,N ' @RecordCount int output ' , @RecordCount output
end
if @PageCurrent = 0 begin
set @sql = ' select top ' + cast ( @PageSize as nvarchar ( 3 )) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrder
end
else begin
if upper ( @FdOrder ) = ' DESC ' begin
set @sql = ' select top ' + cast ( @PageSize as nvarchar ( 3 )) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + ' < ( select min( ' + @IdentityStr + ' ) from (select top ' + cast ( @PageSize * @PageCurrent as nvarchar ( 10 )) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc '
end
else begin
set @sql = ' select top ' + cast ( @PageSize as nvarchar ( 3 )) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + ' > ( select max( ' + @IdentityStr + ' ) from (select top ' + cast ( @PageSize * @PageCurrent as nvarchar ( 10 )) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc '
end
end
-- print @sql
execute ( @sql )
-- select @t = datediff(ms,@t1,getdate())---------------------
405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
