开发者社区> 技术小牛人> 正文

sqlserver2000分页存储过程(原创)

简介:
+关注继续查看
< DOCTYPE html PUBLIC -WCDTD XHTML StrictEN httpwwwworgTRxhtmlDTDxhtml-strictdtd>

      原先放在博客园的文章,无奈那里的大牛实在太多,无人问津,现在放到百度空间来,希望能和大家一起讨论讨论。以下发布这几天写的一个分页存储过程:
功能:海量优化,支持任何字段排序,可支持随机取数据,导出所有数据功能,用了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

本文转自 netcorner 博客园博客,原文链接: http://www.cnblogs.com/netcorner/archive/2007/11/09/2912259.html ,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【推荐】(SqlServer)不公开存储过程sp_Msforeachtable与sp_Msforeachdb详解
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/7207095   【推荐】(SqlServer)不公开存储过程 sp_Msforeachtable与sp_Msforeachdb详解 ——通过知识共享树立个人品牌。
735 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1512 0
Mysql分页查询通用存储过程 - 阿影的黄金时代 - 博客频道 - CSDN.NET
来源:http://blog.csdn.net/fcrpg2005/article/details/1522713#1536434-hi-1-63006-42d97150898b1af15ddaae52f91f09c2
676 0
通用分页存储过程
/*--实现分页的通用存储过程 显示指定表、视图、查询结果的第X页对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法如果视图或查询结果中有主键,不推荐此方法如果使用查询语...
549 0
SqlServer建立存储过程,方便.NET插入自增字段
首先,需要在数据库中创建一个表,以在test数据库创建tableNo表为例: create table tablesNo ( tableName varchar(30) not null, --表名 num int not null --行数 ) 然后在数据库中 --> 可...
777 0
01.SQLServer性能优化之----强大的文件组----分盘存储
汇总篇:http://www.cnblogs.com/dunitian/p/4822808.html#tsql 文章内容皆自己的理解,如有不足之处欢迎指正~谢谢 前天有学弟问逆天:“逆天,有没有一种方式可以让我一个表存到两个数据库文件中,或者说怎么把一个表的数据平摊到其他数据库文件中?” (⊙o⊙)…,逆天数据库优化不是很强悍,不过类似的情景倒是见过,可以给你一个思路。
858 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
17899 0
jdbc_分页查询,大数据,批处理,存储过程
分页查询 实际上就是每次查询一部分记录,并显示: select * from table_name limit StartIndex, PageSize;——>StartIndex:从第几个记录开始查。
759 0
5723
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载