以前写的一个分页存储过程,刚才不小心翻出来的
程序员文章站
2022-05-18 12:52:58
create procedure goalerpagesp@intpagesize int,@intcurrpage int,@st...
create procedure goalerpagesp
@intpagesize int,
@intcurrpage int,
@strfields nvarchar(2000),
@strtable varchar(200),
@strwhere varchar(800),
@strordertype varchar(200),
@strkeyfield varchar(50)
as
set nocount on
declare @tmpsql nvarchar(4000)--存放动态sql语句
declare @tmpwhere varchar(800)
declare @tmpandwhere varchar(800)--用于第n(>1)页上边的查询条件
declare @tmporder varchar(200)
declare @tmpd_x varchar(2)
declare @tmpmin_max varchar(3)
--设置条件--
if @strwhere is null or rtrim(@strwhere)=''
begin --没有查询条件
set @tmpwhere=''
set @tmpandwhere=''
end
else
begin --有查询条件
set @tmpwhere=' where '+@strwhere
set @tmpandwhere=' and '+@strwhere
end
--设置排序--
if @strordertype != 0
begin--倒序
set @tmpd_x = '<'
set @tmpmin_max = 'min'
set @tmporder=' order by ' +@strkeyfield+ ' desc'
end
else
begin
set @tmpd_x = '>'
set @tmpmin_max = 'max'
set @tmporder=' order by ' +@strkeyfield+ ' asc'
end
--sql查询--
if @intcurrpage=1
set @tmpsql='select top '+cast(@intpagesize as varchar)+' '+@strfields+' from '+@strtable+' '+@tmpwhere+' '+@tmporder
else
set @tmpsql='select top '+cast(@intpagesize as varchar)+' '+@strfields+' from '+@strtable+' where ('+@strkeyfield+' '+@tmpd_x+' (select '+@tmpmin_max+'('+@strkeyfield+') from (select top '+cast(@intpagesize*(@intcurrpage-1) as varchar)+' '+@strkeyfield+' from '+@strtable+' '+@tmpwhere+' '+@tmporder+') as t))'+@tmpandwhere+' '+@tmporder
exec(@tmpsql)
go
@intpagesize int,
@intcurrpage int,
@strfields nvarchar(2000),
@strtable varchar(200),
@strwhere varchar(800),
@strordertype varchar(200),
@strkeyfield varchar(50)
as
set nocount on
declare @tmpsql nvarchar(4000)--存放动态sql语句
declare @tmpwhere varchar(800)
declare @tmpandwhere varchar(800)--用于第n(>1)页上边的查询条件
declare @tmporder varchar(200)
declare @tmpd_x varchar(2)
declare @tmpmin_max varchar(3)
--设置条件--
if @strwhere is null or rtrim(@strwhere)=''
begin --没有查询条件
set @tmpwhere=''
set @tmpandwhere=''
end
else
begin --有查询条件
set @tmpwhere=' where '+@strwhere
set @tmpandwhere=' and '+@strwhere
end
--设置排序--
if @strordertype != 0
begin--倒序
set @tmpd_x = '<'
set @tmpmin_max = 'min'
set @tmporder=' order by ' +@strkeyfield+ ' desc'
end
else
begin
set @tmpd_x = '>'
set @tmpmin_max = 'max'
set @tmporder=' order by ' +@strkeyfield+ ' asc'
end
--sql查询--
if @intcurrpage=1
set @tmpsql='select top '+cast(@intpagesize as varchar)+' '+@strfields+' from '+@strtable+' '+@tmpwhere+' '+@tmporder
else
set @tmpsql='select top '+cast(@intpagesize as varchar)+' '+@strfields+' from '+@strtable+' where ('+@strkeyfield+' '+@tmpd_x+' (select '+@tmpmin_max+'('+@strkeyfield+') from (select top '+cast(@intpagesize*(@intcurrpage-1) as varchar)+' '+@strkeyfield+' from '+@strtable+' '+@tmpwhere+' '+@tmporder+') as t))'+@tmpandwhere+' '+@tmporder
exec(@tmpsql)
go
调用方法:
intpagesize=20
strtable=" [tablename] " '数据表名称
strfields=" field1,field2,field3,field4 " '需要读取的列名
strkeyfield="field1" '主键:这里假设field1为主键
strwhere="" '条件:fielda='b'
strordertype=1 '排序方式:1为倒序,0为顺序
currpage=request.querystring("page")
if(currpage<>"" and isnumeric(currpage))then
currpage=clng(currpage)
if(currpage<1)then currpage=1
else
currpage=1
end if
if strwhere<>"" then
tmpwhere=" where "&strwhere
else
tmpwhere=""
end if
if(session("reccount")<>"")then
if(session("strwhere")<>strwhere)then
reccount=conn.execute("select count("&strkeyfield&") from "&strtable&tmpwhere)(0)
session("reccount")=reccount
session("strwhere")=strwhere
else
reccount=session("reccount")
end if
else
reccount=conn.execute("select count(*) from "&strtable&tmpwhere)(0)
session("reccount")=reccount
session("strwhere")=strwhere
end if
if(reccount mod intpagesize <>0)then
intpagecount=int(reccount/intpagesize)+1
else
intpagecount=reccount/intpagesize
end if
set cmd=server.createobject("adodb.command")
cmd.commandtype=4
set cmd.activeconnection=conn
cmd.commandtext="goalerpagesp"
cmd.parameters.append cmd.createparameter("@intpagesize",4,1,4,intpagesize)
cmd.parameters.append cmd.createparameter("@intcurrpage",4,1,4,currpage)
cmd.parameters.append cmd.createparameter("@strfields",200,1,2000,strfields)
cmd.parameters.append cmd.createparameter("@strtable",200,1,200,strtable)
cmd.parameters.append cmd.createparameter("@strwhere",200,1,800,strwhere)
cmd.parameters.append cmd.createparameter("@strordertype",4,1,4,strordertype)
cmd.parameters.append cmd.createparameter("@strkeyfield",200,1,50,strkeyfield)
set rs=cmd.execute()
if reccount<1 then
response.write("没有记录")
else
getrecord=rs.getrows(intpagesize)
for i=0 to ubound(getrecord,2)
response.write(getrecord(0,i),getrecord(1,i),getrecord(2,i)) '...输出内容
next
getrecord=null
end if
set rs=nothing
strtable=" [tablename] " '数据表名称
strfields=" field1,field2,field3,field4 " '需要读取的列名
strkeyfield="field1" '主键:这里假设field1为主键
strwhere="" '条件:fielda='b'
strordertype=1 '排序方式:1为倒序,0为顺序
currpage=request.querystring("page")
if(currpage<>"" and isnumeric(currpage))then
currpage=clng(currpage)
if(currpage<1)then currpage=1
else
currpage=1
end if
if strwhere<>"" then
tmpwhere=" where "&strwhere
else
tmpwhere=""
end if
if(session("reccount")<>"")then
if(session("strwhere")<>strwhere)then
reccount=conn.execute("select count("&strkeyfield&") from "&strtable&tmpwhere)(0)
session("reccount")=reccount
session("strwhere")=strwhere
else
reccount=session("reccount")
end if
else
reccount=conn.execute("select count(*) from "&strtable&tmpwhere)(0)
session("reccount")=reccount
session("strwhere")=strwhere
end if
if(reccount mod intpagesize <>0)then
intpagecount=int(reccount/intpagesize)+1
else
intpagecount=reccount/intpagesize
end if
set cmd=server.createobject("adodb.command")
cmd.commandtype=4
set cmd.activeconnection=conn
cmd.commandtext="goalerpagesp"
cmd.parameters.append cmd.createparameter("@intpagesize",4,1,4,intpagesize)
cmd.parameters.append cmd.createparameter("@intcurrpage",4,1,4,currpage)
cmd.parameters.append cmd.createparameter("@strfields",200,1,2000,strfields)
cmd.parameters.append cmd.createparameter("@strtable",200,1,200,strtable)
cmd.parameters.append cmd.createparameter("@strwhere",200,1,800,strwhere)
cmd.parameters.append cmd.createparameter("@strordertype",4,1,4,strordertype)
cmd.parameters.append cmd.createparameter("@strkeyfield",200,1,50,strkeyfield)
set rs=cmd.execute()
if reccount<1 then
response.write("没有记录")
else
getrecord=rs.getrows(intpagesize)
for i=0 to ubound(getrecord,2)
response.write(getrecord(0,i),getrecord(1,i),getrecord(2,i)) '...输出内容
next
getrecord=null
end if
set rs=nothing
有用的朋友请自己慢慢调试吧,总记录是用asp来取的,存储在session里边,如果每次都统计一次总记录,将会非常费时,当然,如果你想在存储过程里来取总记录和总页数然后返回也是可以的,下边是代码:
--获取记录总数--
set @tmpsql='select @getrecordcounts=count('+@strkeyfield+') from '+@strtable+@tmpwhere
exec sp_executesql @tmpsql,n'@getrecordcounts int output',@getrecordcounts output
--获取总页数--
set @tempfolatnumber=@getrecordcounts%@intpagesize
if @getrecordcounts<=@intpagesize
set @getpagecounts=1
else
begin
if @tempfolatnumber != 0
set @getpagecounts=(@getrecordcounts/@intpagesize)+1
else
set @getpagecounts=(@getrecordcounts/@intpagesize)
end
set @tmpsql='select @getrecordcounts=count('+@strkeyfield+') from '+@strtable+@tmpwhere
exec sp_executesql @tmpsql,n'@getrecordcounts int output',@getrecordcounts output
--获取总页数--
set @tempfolatnumber=@getrecordcounts%@intpagesize
if @getrecordcounts<=@intpagesize
set @getpagecounts=1
else
begin
if @tempfolatnumber != 0
set @getpagecounts=(@getrecordcounts/@intpagesize)+1
else
set @getpagecounts=(@getrecordcounts/@intpagesize)
end
别忘了返回定义参数:
@getrecordcounts int output,--返回总记录
@getpagecounts int output--返回总页数
上一篇: jsp简单实现页面之间共享信息的方法
下一篇: 写了段批量抓取某个列表页的东东