–SQL2005分页存储过程
–要求:分页表必须具备主键
–缺点:只能单表查询分页
–作者:yms123
–所在论坛:
–ASP/ASP.NET技术版主
–MSN:yms126@hotmail.com
CREATE Procedure SQL2005Pagin(
  @PageSize int, –每页记录数
  @PageCurrent int, –当前页
  @PageCount int OUTPUT,–输出参数总页数
澳门新葡萄京所有网站,  @SqlStr varchar(max)=”,–SQL语句
  @TableName varchar(max)–表名
)
AS
–声明变量
Declare @@DataCount int–总记录数
Declare @tempSqlStr nvarchar(max) –临时Sql语句
Declare @tempNumber int –临时变量
Declare @@PKName varchar(max) –主键名称
Declare @LPDNum int –上一页记录数
Declare @CPDnum int –本页记录数
–获得总记录数
Set @tempSqlStr=’select
@DataCount=count(*) from ‘+@TableName+’ Where
exists (‘+@SqlStr+’)’
exec sp_executesql @tempSqlStr,N’@DataCount
int out’,@@DataCount out –统计记录数
–print @@DataCount
–获得指定表的主键字段名称
select TOP 1 @@PKName=[name]
from syscolumns a where exists
(
    select * from sysindexkeys b
    where b.id=object_id(N’tbl_User’)
    and a.id=b.id
    and a.colid=b.colid
)

CREATE Procedure FramWorkPage @TableName varchar(50), –表名 @Fields
varchar(5000) = ‘*’, –字段名(全部字段为*) @OrderField varchar(5000),
–排序字段(必须!支持多字段) @sqlWhere varchar(5000) =
Null,–条件语句(不用加where) @pageSize int, –每页多少条记录 @pageIndex
int = 1 , –指定当前为第几页 @TotalPage int output, –返回条数
@OrderType bit — 设置排序类型,1 升序 0 值则降序 as begin declare
@strOrder varchar(400) — 排序类型

–计算总页数
if (@@DataCount%@PageSize)=0
    Set @PageCount=@@DataCount/@PageSize
Else
    Begin
       Set @tempNumber=@@DataCount/@PageSize
       Set @PageCount=@tempNumber+1
    End

Begin Tran –开始事务 Declare @sql nvarchar(4000); Declare @totalRecord
int; –计算总记录数 if (@SqlWhere =”” or @SqlWhere=” or @sqlWhere is
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–计算总记录数

–计算当前页记录数
Set @LPDNum=(@PageCurrent-1)*@PageSize
Set @CPDnum=@PageSize+@LPDNum

–计算总页数

–构造查询语句筛选出记录
Set @tempSqlStr=’WITH
[‘+@TableName+’ ORDERED BY
RowNum] AS’+CHAR(13)
Set @tempSqlStr=@tempSqlStr+'(‘+CHAR(13)
Set @tempSqlStr=@tempSqlStr+’select
ROW_NUMBER() OVER (ORDER BY ‘+@@PKName+’ ASC) as
RowNum,* ‘
Set @tempSqlStr=@tempSqlStr+’from
‘+@TableName+’ Where
exists ‘+CHAR(13)
Set @tempSqlStr=@tempSqlStr+'(‘+CHAR(13)
Set @tempSqlStr=@tempSqlStr+@SqlStr+CHAR(13)
Set @tempSqlStr=@tempSqlStr+’)’+CHAR(13)
Set @tempSqlStr=@tempSqlStr+’)’+CHAR(13)
Set @tempSqlStr=@tempSqlStr+’select *
from [‘+@TableName+’ ORDERED BY
RowNum] as a inner join (‘
Set @tempSqlStr=@tempSqlStr+’select
RowNum,’+@@PKName+’ from
(select RowNum,’+@@PKName+’ from
[‘+@TableName+’ ORDERED BY
RowNum])’
Set @tempSqlStr=@tempSqlStr+’as t where
rowNum between ‘+cast(@LPDNum as varchar)+’ and
‘+cast(@CPDnum as varchar)+’) as b on
a.’+@@PKName+’=b.’+@@PKName+’ order by
b.rownum’

select @TotalPage=@totalRecord –CEILING((@totalRecord+0.0)/@PageSize)

–执行查询语句返回结果
exec(@tempSqlStr)

if @OrderType = 0 begin set @strOrder = ‘ order by [‘ + @OrderField
+’] desc’ –如果@OrderType是0,就执行降序,这句很重要! end else begin
set @strOrder = ‘ order by [‘ + @OrderField +’] asc’ end

使用SQL2005独有的ROW_NUMBER函数实现,
已做压力测试
总数据量:1329382
条数据的表
一百万条数据1页测试执行10次最少时间用了53秒
欢迎提出改进意见
测试代码
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate() declare @@PCount int
exec SQL2005Pagin 1000000,1,@@PCount,’select * from
tbl_User’,’tbl_User’
PRINT @@PCount select datediff(ms,@begin_date,@end_date) as
‘用时/毫秒’
tbl_User为临时测试的表名已插入1329382条数据
测试电脑的配置(单位的电脑)[QQ电脑管家获得的配置信息]
操作系统  Microsoft Windows XP Professional (32位/Service Pack 3)
CPU  (英特尔)Pentium(R) Dual-Core  CPU      E5800  @ 3.20GHz(3192 MHz)
主板  soyo SY-I5G41-L V5.0
内存  2.00 GB (金士顿 PC3-10600 DDR3 SDRAM 1333MHz)
主硬盘  500 GB (希捷 ST3500413AS 已使用时间: 294小时)
显卡  Intel(R) G41 Express Chipset (256 MB)
显示器  戴尔 DELf021 32位真彩色 60Hz
声卡  Realtek 5.1 High Definition Audio
网卡  Realtek PCIe FE Family Controller

if (@SqlWhere =”” or @SqlWhere=” or @sqlWhere is NULL) set @sql =
‘Select * FROM (select ROW_NUMBER() Over( ‘+@strOrder+’ ) as rowId,’ +
@Fields + ‘ from ‘ + @TableName else set @sql = ‘Select * FROM (select
ROW_NUMBER() Over( ‘+@strOrder+’ ) 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

if @OrderType = 0 begin set @strOrder = ‘ order by rowid desc’
–如果@OrderType是0,就执行降序,这句很重要! end else begin set
@strOrder = ‘ order by rowid asc’ end –继续合成sql语句 set @Sql = @Sql

  • ‘) as ‘ + @TableName + ‘ where rowId between ‘ +
    Convert(varchar(50),@StartRecord) + ‘ and ‘ +
    Convert(varchar(50),@EndRecord) + ‘ ‘+@strOrder — print @sql Exec(@Sql)
    ————————————————— If @@Error 0 Begin
    RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord
    —返回记录总数 End end SELECT TOP 10 * FROM TestTable WHERE (ID
    (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS
    T)) ORDER BY ID

SELECT TOP 页大小 * FROM TestTable WHERE (ID (SELECT MAX(id) FROM
(SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T)) ORDER BY ID

SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM
TestTable ORDER BY id)) ORDER BY ID

SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP
页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID

create procedure SqlPager @sqlstr nvarchar(4000), –查询字符串
@currentpage int, –第N页 @pagesize int –每页行数 as set nocount on
declare @P1 int, –P1是游标的id @rowcount int exec sp_cursoropen @P1
output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output select
ceiling(1.0*@rowcount/@pagesize) as 总页数–,@rowcount as
总行数,@currentpage as 当前页 set
@currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch
@P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off