原先放在博客园的文章，无奈那里的大牛实在太多，无人问津，现在放到百度空间来，希望能和大家一起讨论讨论。以下发布这几天写的一个分页存储过程:
功能：海量优化，支持任何字段排序，可支持随机取数据，导出所有数据功能，用了id（整型）比较方法
问题:
1.这里面运用到了临时表变量和临时表，在使用这个的时候也没感觉用临时表变量快，不知道是什么原因，请各位大虾分析一下.
2.尾页数据优化，折半式的方式，不过只用了一次折半，多次的话好像行不通，不知道是不是其它好方法，
3.数据量小的时候采用临时表，当超过@RSCOUNT所指定数时采用临时表
4.这里面临时表和临时表变量的使用是在不是主键排序时采用的，因为非主键排序时通常是可能存在重复
5.如果表中用guid的话好像很麻烦，在想是不是不用id比较的方法采用in的方法，目前guid这个还没考虑进去
6.可由用户定制是否返回记录总数(当非主键排序时一定需要这个返回值的)
7.考虑到注入这问题的话,我想一般是在程序里面的自动去掉一些特殊的字符的,这里面就没考虑这个是不是可以注入的,请大家看一看是不是可以注入的
以下是代码：
------------------------------------------------------------------------------------------------------------------------
-- Function: 返回记录集
-- Date Created: 2007年7月14日
-- Created By: sjf http://netcorner.cnblogs.com
--last update:2007.11.16
--declare @intReturn int
--exec USP_Pagination 'test','*','id',10,99998,0,'id','',0,1,@intReturn output
------------------------------------------------------------------------------------------------------------------------
-- 获取指定页的数据
CREATE PROCEDURE USP_Pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(500) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页显示记录数
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@tblPk varchar(100)='id',--表的主键字段(可省略)
@strWhere varchar(1000) = '',-- 查询条件 (注意: 不要加 where)
@intRand int=0,--是否需要随机显示数据,只适合第一页的情况，0表示不需要
@intReturnState int=1,--是否需要返回值,1为需要
@Result int output --返回页数值
AS
set nocount on
if(@PageIndex<1) return
declare @strSQL nvarchar(4000) -- 主语句
declare @strCount varchar(4000) --统计
declare @strTmp varchar(100) -- 临时变量
declare @strTmp1 varchar(100) --临时
declare @strOrder varchar(200) -- 排序类型
declare @strOrder1 varchar(200) --排序折半处理
declare @RSCOUNT int --多少条记录时用临时表变量
set @RSCOUNT=100000 --临时表可能需要开销 4*4*100000 byte
declare @RSFOOTER int --记录尾部超过时处理开关
set @RSFOOTER=1000
set @strCount=""
declare @strWhere1 varchar(1000) --条件多个连接情况使用
set @strWhere1=""
if @strWhere != ''
begin
set @strWhere1=" and ("+")"
set @strWhere=" where "+" "
end
if @intReturnState=1
set @strCount="select @intTotal=count(*) from " +"; "
if @OrderType != 0 --排序类型
begin
set @strTmp = "<(select min"
set @strTmp1 = "<=(select max"
set @strOrder = " order by " + @fldName +" desc"
set @strOrder1 = " order by " + @fldName +" asc"
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = ">(select max"
set @strTmp1 = ">=(select min"
set @strOrder = " order by " + @fldName +" asc"
set @strOrder1 = " order by " + @fldName +" desc"
end
if @PageIndex = 1 --第一页的处理,如果是第一页就执行以上代码，这样会加快执行速度
begin
declare @top nvarchar(100)
if(@PageSize<1)
set @top=" "
else
set @top=" top " + str(@PageSize) +" "
if(@intRand=0) --是否需要随便产生
set @strSQL = "select " + @top + " from " + @tblName + @strWhere + " " + @strOrder
else
begin
set @strSQL="select " + @top + @strGetFields+ " from " + @tblName + @strWhere + " order by newid()"
end
set @strSQL=@strCount+@strSQL
end
else --其它页的处理
begin
if @fldName!=@tblPk --非主键排序创建临时表或临时表变量
begin
declare @strTmptbl char(10);
set @strTmptbl="temptable"
if(@PageSize*@PageIndex>@RSCOUNT) --记录数大于@RSCOUNT时采用临时表处理否则就用表变量
begin
set @strTmptbl="#temptable"
set @strSQL="select top "+str(@PageSize*@PageIndex)+" newid = cast("+" AS int),tempid = IDENTITY (int, 1, 1) INTO "+" FROM "+ @tblName + @strWhere + @strOrder +";"
end
else
begin
set @strTmptbl="@tmpTable"
set @strSQL="DECLARE "+" TABLE([newid] [int] NOT NULL,[tempid] [int] IDENTITY (1, 1) NOT NULL); "+
"INSERT into "]) select top "+str(@PageSize*@PageIndex)+" "+" FROM "+ @tblName + @strWhere + @strOrder +";"
end
set @strSQL=@strCount
top "+str(@PageSize)+" "+" from "+" where [" +"] in(SELECT [newid] FROM "+" WHERE (tempid >"+str(@PageSize*(@PageIndex-1))+")) "
end
else
begin
if(@strCount!="")
set @strCount=@strCount
+"declare @t int; "
+" set @t="+str(@PageSize*(@PageIndex-1))+";"
+" if(@t>@intTotal/2 and @intTotal>"+str(@RSFOOTER)+")"
+" begin "
+"set @t=@intTotal-@t; "
+"if(@t<1) "
+"begin "
+"set @intTotal=0; "
+"return; "
+"end "
+" exec('select top " + str(@PageSize)+" "+" from "+" where ["+"] "+"]) from (select top "+" from "+" "+replace(@strWhere,"'","''")+@strOrder1+") as t1)"+replace(@strWhere1,"'","''")+" ");"
+" end "
+" else "
set @strSQL
+"select top " + str(@PageSize) +" "+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] " + @strWhere + " "
+ @strOrder + ") as tblTmp) "
end
end
print @strSQL
if @intReturnState=1
exec sp_executesql @strSQL,N'@intTotal int output',@Result output
else
exec (@strSQL)
GO