通用带分页的sql2000存储过程和asp调用方法

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/coolwu/article/details/70037001
通过百度搜索带颁的存储过程,虽然有很多,但都是问题很多,而且没有写明如何带条件的查询等,写个文章太不负责任了,经过长时间的研究,本人打造了自己的带分页存储过程,里面可以定义表名,列名,当然你也可以将表名和列名使用传递的方式,我觉得没什么必要,所以没有用传递,只是在内部进行设定,以下源码:
(你基本上不用修改很多东西,只需要把表名和字段名(以下描红的地方)改一下就行了,复杂的问题都在调用时的条件内容上)

create procedure test
(

/* pagethis是当前页码,pagesize是每页多少条记录,tj是反馈的条件,allsl是返回记录总数 */
@pagethis int,@pagesize int,@tj nvarchar(300),
@allsl int=0 output
)
as
begin

declare @sql nvarchar(500);
declare @alltj1 nvarchar(300);--结合where使用
declare @alltj2 nvarchar(300);--结合and使用
declare @tabname nvarchar(30);--表名称
declare @tablie nvarchar(300);--要选择字段名(列名)
/*初始化条件*/
set @tabname=' news ';
set @tablie=' id,title ';

set @alltj1='';set @alltj2='';
if @tj<>''
begin
set @alltj1=' where '+@tj;set @alltj2=' and '+@tj;
end
if (@pagethis=0 and @pagesize=0)
/*统计记录总数*/
begin
set nocount on;
set @sql='select count(id) as allsl from '+@tabname+@alltj1;
exec(@sql);
set nocount off;
end
else
/*取数据记录*/
begin
set nocount on;
if @pagethis=1
begin
set @sql='select top '+str(@pagesize)+@tablie+' from '+@tabname+@alltj1+' order by id desc';
end
else
begin
set @sql='select top '+str(@pagesize)+@tablie+' from '+@tabname+' where (id<(select min(id) from (select top '+str(@pagesize*(@pagethis-1))+' id from '+@tabname+@alltj1+' order by id desc) as temptable)) '+@alltj2+' order by id desc';
end
execute(@sql);
set nocount off;
end

end
go

以下是asp调用方法:

<%

'如果条件为空,那么gctj="''",注意,这里的内容是双引号里面有两个单引号,条件的前面不用带where
'看里面的条件,最左最右是有一个单引号包含的,如果中间的内容有单引号,可用两个连续的单引号来替换
gctj=" ' (title like ''%中%'' or convert(nvarchar(300),tjly) like ''%中%'')' "
gcname=" test "
'取总数
set test=conn.execute("exec "&gcname&" 0,0,"&gctj)
allsl=test("allsl"):set test=nothing
'分页设定
totalrec=allsl:ipagesize=20:currentpage=getint(get1("page"))
n=totalrec\ipagesize:if totalrec mod ipagesize<>0 then n=n+1
if currentpage>n then currentpage=n
if currentpage<1 then currentpage=1
'用存储过程调用数据
rs.open "exec "&gcname¤tpage&","&ipagesize&","&gctj,conn,1,1
do while not rs.eof
%>
<div><%=rs("name")%></a></div>
<%rs.movenext:loop:rs.close%>
<!--pagelist-->
这里是分页代码,用你的分页代码配合使用即可
<!--pagelist-->
展开阅读全文

通用分页存储过程

03-09

经过测试,如相存储过程效率非常高。但在排序方面有个小问题,如:order by abs(IP-2130706434)无效,请问要如何修改?rnrnrn[code=SQL]rnrnrnCreate PROC [dbo].[sp2_PageView] rn /* rn nzperfect [no_mIss] 高效通用分页存储过程(双向检索) rn 敬告:适用于单一主键或存在唯一值列的表或视图 rn ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 rn */ rn @TableName VARCHAR(200), --表名 rn @FieldList VARCHAR(2000), --显示列名,如果是全部字段则为* rn @PrimaryKey VARCHAR(100), --单一主键或唯一值键 rn @Where VARCHAR(2000), --查询条件 不含'where'字符,如id>10 and len(userid)>9 rn @Order VARCHAR(1000), --排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc rn --注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷 rn @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 rn @RecorderCount INT, --记录总数 0:会返回总记录 rn @PageSize INT, --每页输出的记录数 rn @PageIndex INT, --当前页数 rn @TotalCount INT OUTPUT , --记返回总记录 rn @TotalPageCount INT OUTPUT --返回总页数 rnAS rnSET NOCOUNT ON rn IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0 rn SET @Order = RTRIM(LTRIM(@Order)) rn SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) rn SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','') rn WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0 rn BEGIN rn SET @Order = REPLACE(@Order,', ',',') rn SET @Order = REPLACE(@Order,' ,',',') rn END rn IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = '' rn or ISNULL(@PrimaryKey,'') = '' rn or @SortType < 1 or @SortType >3 rn or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0 rn BEGIN rn PRINT('ERR_00') rn RETURN rn END rn IF @SortType = 3 rn BEGIN rn IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC') rn BEGIN PRINT('ERR_02') RETURN END rn END rn DECLARE @new_where1 VARCHAR(1000) rn DECLARE @new_where2 VARCHAR(1000) rn DECLARE @new_order1 VARCHAR(1000) rn DECLARE @new_order2 VARCHAR(1000) rn DECLARE @new_order3 VARCHAR(1000) rn DECLARE @Sql VARCHAR(8000) rn DECLARE @SqlCount NVARCHAR(4000) rn IF ISNULL(@where,'') = '' rn BEGIN rn SET @new_where1 = ' ' rn SET @new_where2 = ' Where ' rn END rn ELSE rn BEGIN rn SET @new_where1 = ' Where ' + @where rn SET @new_where2 = ' Where ' + @where + ' AND ' rn END rn IF ISNULL(@order,'') = '' or @SortType = 1 or @SortType = 2 rn BEGIN rn IF @SortType = 1 rn BEGIN rn SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC' rn SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC' rn END rn IF @SortType = 2 rn BEGIN rn SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC' rn SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC' rn END rn END rn ELSE rn BEGIN rn SET @new_order1 = ' orDER BY ' + @Order rn END rn rn IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0 rn BEGIN rn SET @new_order1 = ' orDER BY ' + @Order rn SET @new_order2 = @Order + ',' rn SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','ASC,'),'DESC,','DESC,') rn SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','DESC,'),'DESC,','ASC,') rn SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1) rn IF @FieldList <> '*' rn BEGIN rn SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') rn SET @FieldList = ',' + @FieldList rn WHILE CHARINDEX(',',@new_order3)>0 rn BEGIN rn IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0 rn BEGIN rn SET @FieldList = rn @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3)) rn END rn SET @new_order3 = rn SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3)) rn END rn SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) rn END rn END rn rn SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' rn + CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T' rn IF @RecorderCount = 0 rn BEGIN rn EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', rn @TotalCount OUTPUT,@TotalPageCount OUTPUT rn END rn ELSE rn BEGIN rn Select @TotalCount = @RecorderCount rn END rn IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) rn BEGIN rn SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) rn END rn IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) rn BEGIN rn IF @PageIndex = 1 --返回第一页数据 rn BEGIN rn SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' rn + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1 rn END rn IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 rn BEGIN rn SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' rn + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) rn + ' ' + @FieldList + ' FROM ' rn + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' rn + @new_order1 rn END rn END rn [/code]rnrn 论坛

SQL 通用分页存储过程

09-22

大侠们,帮看看 这个通用分页存储过程 效率怎么样?rnrn还有就是能不能帮忙注解一下每行 都是什么意思?rnrn如何改进?rnrn大侠 小弟菜鸟,想多学习学习,请大侠帮助,分数不多,请多见谅rnrn[code=SQL]rnrnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrngornrnALTER PROCEDURE [dbo].[Page]rn@tblName varchar(255), -- 表名rn@fldName varchar(255), -- 主键字段名rn@PageSize int = 10, -- 页尺寸rn@PageIndex int = 1, -- 页码rn@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回rn@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序rn@OrderField varchar(255)='', --设置排序字段 如果该字段为空,默认主键排序rn@strWhere varchar(1000) = '', -- 查询条件 (注意: 不要加 where)rn@tableJoin varchar(1000)='', --表连接rn@tableField varchar(1000)='' --表字段rnrnASrndeclare @strSQL varchar(6000) -- 主语句rndeclare @strTmp varchar(1000) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)rndeclare @strOrder varchar(400) -- 排序类型rnrnif @tableField =''rnset @tableField='*'rnrnif @OrderField !=''rnset @strOrder = ' order by [' + @OrderField +']'rnelse rnset @strOrder = ' order by [' + @fldName +']'rnrnif @OrderType != 0rnbeginrnset @strTmp = '<(select min'rnset @strOrder = @strOrder + ' desc'rnendrnelsernbeginrnset @strTmp = '>(select max'rnset @strOrder = @strOrder + ' asc'rnendrnset @strSQL = 'select top ' + str(@PageSize) + ' * from ['rn+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['rn+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['rn+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'rn+ @strOrderrnif @strWhere != ''rnset @strSQL = 'select top ' + str(@PageSize) + ' * from ['rn+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['rn+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['rn+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' 'rn+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderrnif @PageIndex = 1rnbeginrnset @strTmp =''rnif @strWhere != ''rnset @strTmp = ' where ' + @strWherernset @strSQL = 'select top ' + str(@PageSize) + @tableField +' from ['rn+ @tblName + '] '+ @tableJoin + @strTmp + ' ' + @strOrderrnendrnif @IsReCount != 0rnset @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWherernrnexec (@strSQL)rn[/code] 论坛

通用DB2存储过程分页

12-26

CREATE PROCEDURE SALES.DB2PAGINATION(IN ITBNAME VARCHAR(2000), -- 表名rn IN ISHOWFIELD VARCHAR(1000), -- 显示字段rn IN IJOIN VARCHAR(1000), -- 联接条件(如:内联、外联)rn IN IWHERE VARCHAR(2000), -- 查询条件 (注意: 不要加 WHERE)rn IN IORDER VARCHAR(100), -- 排序条件 (注意: 不要加 ORDER BY)rn IN IPAGESIZE INTEGER, -- 页尺寸 如果为0 默认返回前一百万条数据 可以认为是返回所有数据rn INOUT IOCURRENTPAGEIX INTEGER, -- 输入和输出:当前页rn OUT OPAGESTARTROW INTEGER, -- 输出:当前开始行rn OUT OPAGEENDROW INTEGER, -- 输出:当前结束行rn OUT OTOTALROWS INTEGER, -- 输出:当前总记录数rn OUT OHASPREVIOUSPAGE INTEGER, -- 输出:是否有上一页rn OUT OHASNEXTPAGE INTEGER, -- 输出:是否有下一页rn OUT OTOTALPAGES INTEGER, -- 输出:总页数rn OUT OERROR VARCHAR(100)) -- 输出:错误信息rn RESULT SETS 1rn MODIFIES SQL DATArn NOT DETERMINISTICrn LANGUAGE SQLrnBEGINrn/**//*----------------------------------------------------------------rn * Copyright (C) 2006 Huaciusrn * 版权所有。 rn * rn * 存储过程分页rn *rn * MSN: Huacius@msn.comrn//-----------------------------------------------------------------------*/rn DECLARE STRSQL VARCHAR(6000); -- 主语句rnrn DECLARE result CURSOR WITH RETURN TO CALLER FOR S2;rn rn DECLARE exit handler FOR sqlexception -- 异常捕获rn BEGINrn set OERROR = 'error!';rn END;rn rn -- BODY start --rn if(iwhere <> '') thenrn set iwhere = ' where ' || iwhere;rn end if;rn if(iorder <> '') thenrn set iorder = 'order by ' || iorder;rn end if;rn if(ijoin <> '') thenrn set ijoin = ' ' || ijoin;rn end if;rn rn set strsql = 'select count(*) from ' || itbname || ijoin || iwhere;rn prepare s2 from strsql;rn open result;rn fetch result into ototalrows; -- 总记录数rn close result;rnrn if(ipagesize = 0) thenrn set ipagesize = 1000000; -- 每页显示数rn end if;rnrn set ototalpages = (ototalrows - 1) / ipagesize + 1; -- 总页数rnrn if(iocurrentpageix < 1) thenrn set iocurrentpageix = 1; -- 当前页rn elsern if(iocurrentpageix > ototalpages) thenrn set iocurrentpageix = ototalpages;rn end if;rn end if;rnrn set opagestartrow = ipagesize * (iocurrentpageix -1) + 1; -- 每页开始数rn if(iocurrentpageix = ototalpages) thenrn set opageendrow = ototalrows; -- 每页结束数rn elsern set opageendrow = ipagesize * iocurrentpageix;rn end if;rnrn if(iocurrentpageix > 1) thenrn set ohaspreviouspage = 1; -- 是否有上一页rn elsern set ohaspreviouspage = 0;rn end if;rnrn if(iocurrentpageix < ototalpages) thenrn set ohasnextpage = 1; -- 是否有下一页rn elsern set ohasnextpage = 0;rn end if;rnrn set strsql = 'select * from (select rownumber() over(' || iorder || ') as rownum,' rn || ishowfield rn || ' from ' rn || itbname rn || ijoin rn || iwherern || ') as temp where rownum between ' || rtrim(char(opagestartrow)) || ' and ' || rtrim(char(opageendrow));rn prepare s2 from strsql;rn open result;rn -- BODY end --rnrnENDrnrnrn你对此存储过程有何改进建议? 论坛

通用Asp分页函数

09-19

<%rnfunction GetPageList(curpage,rscount,pagesize,pagelistnum)rnrn'#####################版权声明#############################rn'版权所有:遨天网络 http://www.allting.netrn'作者:风逍遥(mendel) mendel@allting.netrn'您可以随意转摘或使用本函数,但必须保留本版权信息rn'##########################################################rnrn'=====================参数说明=============================rn'curpage当前页rn'rscount总记录数rn'pagesize显示数据条数rn'pagelistnum显示几个分页链接rn'==========================================================rnrn'声明函数rndim pgcount,firstpage,endpage,lastpage,firststr,endstr,pagelist,pgcount1,addpagern rn'获取总页数rnpgcount1=split((rscount/pagesize),".")rnpgcount=pgcount1(0)rnlastpage=rscount mod pagesizernif lastpage>0 thenrn pgcount=pgcount+1rnend ifrnrn'判断页数是否超出rnif clng(curpage)>clng(pgcount) thenrn curpage=1rnend ifrnrn'计算第一页rnif len(curpage)>1 thenrn if clng(curpage mod pagelistnum)=0 thenrn firstpage=clng(left(curpage,len(curpage)-1))*pagelistnum-pagelistnum+1rn elsern firstpage=clng(left(curpage,len(curpage)-1))*pagelistnum+1rn end ifrnelsern firstpage=1rnend ifrnrn'计算最后页rnendpage=firstpage+pagelistnum-1rnif clng(endpage)>clng(pgcount) thenrn endpage=pgcountrnend ifrnrn'计算前一个pagelistnumrnif clng(curpage)>clng(pagelistnum) thenrn rn firststr="<< "rnelsern firststr="<< "rnend ifrn'计算前一页rnif clng(curpage)>1 and clng(pgcount)>1 thenrn firststr=firststr & "< "rnelsern firststr=firststr & "< "rnend ifrnrn'计算后一页rnif clng(curpage)1 thenrn endstr="> "rnelsern endstr="> "rnend ifrnrn'计算后一个pagelistnumrnif clng(pgcount)-clng(endpage)>0 thenrn if clng(curpage)-clng(pgcount)>clng(pagelistnum) thenrn addpage=pagelistnumrn elsern addpage=clng(pgcount)-clng(endpage)rn end ifrn endstr=endstr &">>"rnelsern endstr=endstr&">>"rnend ifrnrn'获得循环页数rnfor i=firstpage to endpagern if clng(i)=clng(curpage) thenrn pagelist=pagelist&""&i&" "rn elsern pagelist=pagelist & ""&i&" "rn end ifrnnextrnrn'返回函数值rnGetPageList=" 共有"&rscount&"条记录/"&pgcount&"页 当前页:"&curpage&"/"&pagelistnum&"条记录每页 "&firststr & pagelist & endstr&""rnend functionrnrnpage=request.querystring("page")rnif not isnumeric(page) or page="" thenrn page=1rnend ifrnresponse.write GetPageList(page,0,20,10)rnrn%>rnrnrnrn希望大家能够用得上,呵呵 论坛

没有更多推荐了,返回首页