2005-1-4
有关使用存储过程创建分页

今天查询有关sql server 索引资料 无意在篇文章末尾发现个有关使用存储过程创建分页代码直接复制到查询分析器里有误整理调试了个搬上来起学习下

注:原作者 freedk 以下为笔者整理 这是个相对最优思路方法在大量数据集中表现突出

建立个 Web 应用分页浏览功能必不可少这个问题是数据库处理中十分常见问题经典数据分页思路方法是:ADO 纪录集分页法也就是利用ADO自带分页功能(利用游标)来实现分页但这种分页思路方法仅适用于较小数据量情形游标本身有缺点:游标是存放在内存中很费内存游标建立就将相关记录锁住直到取消游标游标提供了对特定集合中逐行扫描手段般使用游标来逐行遍历数据根据取出数据条件区别进行区别操作而对于多表和大表中定义游标(大数据集合)循环很容易使进入个漫长等待甚至死机
　　更重要是对于非常大数据模型而言分页检索时如果按照传统每次都加载整个数据源思路方法是非常浪费资源现在流行分页思路方法般是检索页面大小块区数据而非检索所有数据然后单步执行当前行

思路来源:
从publish 表中取出第 n 条到第 m 条记录:
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
(SELECT TOP n-1 id
　　　　 FROM publish))

存储过程:

CREATE PROCEDURE pagination3
@tblName varchar(255) , -- 表名
@strGetFields varchar(1000) = \'*\', -- 需要返回列
@fldName varchar(255)=\'\', -- 排序字段名
@PageSize = 10, -- 页尺寸
@PageIndex = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = \'\' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

@doCount != 0
begin
@strWhere !=\'\'
@strSQL = \'select count(*) Total from where \'+@strWhere

@strSQL = \'select count(*) Total from \'
end
--以上代码意思是如果@doCount传递过来不是0就执行总数统计以下所有代码都是@doCount为0情况:


begin
@OrderType != 0
begin
@strTmp = \'<(select min\'
@strOrder = \' order by [\' + @fldName +\'] desc\'
--如果@OrderType不是0就执行降序这句很重要！

end

begin
@strTmp = \'>(select max\'
@strOrder = \' order by [\' + @fldName +\'] asc\'
end

@PageIndex = 1
begin
@strWhere != \'\'

@strSQL = \'select top \' + str(@PageSize) +\' \'+@strGetFields+ \' from [\' + @tblName + \'] where \' + @strWhere + \' \' + @strOrder


@strSQL = \'select top \' + str(@PageSize) +\' \'+@strGetFields+ \' from [\'+ @tblName + \'] \'+ @strOrder
--如果是第页就执行以上代码这样会加快执行速度
end

begin
@strSQL = \'select top \' + str(@PageSize) +\' \'+@strGetFields+ \' from [\'
+ @tblName + \'] where [\' + @fldName + \']\' + @strTmp + \'([\'
+ @fldName + \'])　from (select top \' + str((@PageIndex-1)*@PageSize) + \' [\'
+ @fldName + \'] 　from [\' + @tblName + \']\' + @strOrder + \') as tblTmp)\'


+ @strOrder

@strWhere != \'\'
@strSQL = \'select top \' + str(@PageSize) +\' \'+@strGetFields+ \' from [\'
+ @tblName + \'] where [\' + @fldName + \']\' + @strTmp + \'([\'
+ @fldName + \']) from (select top \' + str((@PageIndex-1)*@PageSize) + \' [\'
+ @fldName + \'] from [\' + @tblName + \'] where \' + @strWhere + \' \'
+ @strOrder + \') as tblTmp) and \' + @strWhere + \' \' + @strOrder
end
end
exec (@strSQL)
--pr @strSQL
GO


以上代码在winxp_sp2 sql server2000 personal 下调试通过