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=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
有用的朋友请自己慢慢调试吧，总记录是用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
别忘了返回定义参数：
@getrecordcounts int output,
--返回总记录@getpagecounts int output
--返回总页数
如对本文有疑问，
点击进行留言回复！！
我要评论
网友评论