俺从http://czcz1024.blogchina.com/转来滴
转自csdn
作者:铁拳
/*
经测试,在 14483461 条记录中查询第 100000 页,每页 10 条记录按升序和降序第一次时间均为 0.47 秒,第二次时间均为 0.43 秒,测试语法如下:
exec GetRecordFromPage news,newsid,10,100000
news 为 表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。
*/
/*
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明: @tblName 包含数据的表名
@fldName 关键字段名
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件 (注意: 不要加 where)
作 者: 铁拳
邮 箱: sunjianhua_kki@sina.com
创建时间: 2004-07-04
修改时间: 2004-07-04
*/
CREATE PROCEDURE GetRecordFromPage
@tblName varchar ( 255 ), -- 表名
@fldName varchar ( 255 ), -- 字段名
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
@OrderType bit = 0 , -- 设置排序类型, 非 0 值则降序
@strWhere varchar ( 2000 ) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar ( 6000 ) -- 主语句
declare @strTmp varchar ( 1000 ) -- 临时变量
declare @strOrder varchar ( 500 ) -- 排序类型
if @OrderType != 0
begin
set @strTmp = " < ( select min "
set @strOrder = " order by [ " + @fldName +" ] desc "
end
else
begin
set @strTmp = " > ( select max "
set @strOrder = " order by [ " + @fldName +" ] asc "
end
set @strSQL = " select top " + str ( @PageSize ) + " * from [ "
+ @tblName + " ] where [ " + @fldName + " ] " + @strTmp + "( [ "
+ @fldName + " ] ) from ( select top " + str (( @PageIndex - 1 ) * @PageSize ) + " [ "
+ @fldName + " ] from [ " + @tblName + " ] " + @strOrder + ") as tblTmp)"
+ @strOrder
if @strWhere != ''
set @strSQL = " select top " + str ( @PageSize ) + " * from [ "
+ @tblName + " ] where [ " + @fldName + " ] " + @strTmp + "( [ "
+ @fldName + " ] ) from ( select top " + str (( @PageIndex - 1 ) * @PageSize ) + " [ "
+ @fldName + " ] from [ " + @tblName + " ] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
if @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where (" + @strWhere + ")"
set @strSQL = " select top " + str ( @PageSize ) + " * from [ "
+ @tblName + " ] " + @strTmp + " " + @strOrder
end
exec ( @strSQL )
GO