利用存储过程分页(asp实例)
存储过程:
create procedure proc_setPage2 --要修改过程把Create改为alter
(
@TblName varchar(100), --200表名
@ID varchar(50), --200表的主键
@FldName varchar(200)='*', --200要显示的列名,如id,name,tel,....
@StrWhere varchar(500)='', --200排序条件,格式：不用写where
@OrderType int = 0, --11设置排序类型, 1降序，0升
@PageSize int, --3每页显示多少条记录
@CurrentPage int --3当前页，pageNo
)
AS
SET NOCOUNT ON --当 SET NOCOUNT 为 ON 时，存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
declare @strSQL varchar(2000) -- 主语句
declare @strTmp varchar(1000) -- 临时SQL变量
declare @strOrderBy varchar(50) -- 临时排序变量
declare @strOrder varchar(500) -- 排序类型
declare @recordCount varchar(1000) -- 总记录主语句,即RecordCount
--declare @PageCount int -- 总页数
/*--=======判断排条件,格式===========
if @StrWhere <> ''
set @StrWhere=' firstName like ''%'+@StrWhere+'%'' and lastName like ''%'+@StrWhere+'%'''
*/
--=======判断排序条件,格式===========
if @OrderType <> 0 --即降序
begin
set @strOrderBy = '<(select min'
set @strOrder = ' order by [' + @ID + '] desc'
end
else
begin
set @strOrderBy = '>(select max'
set @strOrder = ' order by [' + @ID +'] asc'
end
--============默认情况的SQL===========
Set @strTmp =' Select top ' + str(@PageSize) +' '+ @FldName +' from '+ @TblName
--=========判断当前是否第一页，并执行相应的代码，这样会加快执行速度===============
if @CurrentPage =1
Begin
if @StrWhere <> ''
Begin
Set @strSQL=@strTmp +' Where '+@StrWhere+' '+@strOrder
set @recordCount='select count('+@ID+') as RecordCount from '+ @TblName+' Where '+@StrWhere
End
else
Begin
Set @strSQL=@strTmp +' '+@strOrder
set @recordCount='select count('+@ID+') as RecordCount from '+ @TblName
End
End
else
Begin
if @StrWhere <> '' --有where条件查询时
Begin
Set @strSQL=@strTmp + ' where '+@ID+' '+ @strOrderBy+'('+@ID+') from (select top ' + str((@CurrentPage-1)*@PageSize)+' '+@ID+' from '+ @TblName+' Where '+@StrWhere+' '+@strOrder+' ) as tblTmp ) and '+ @StrWhere +' '+ @strOrder
set @recordCount='select count('+@ID+') as RecordCount from '+ @TblName+' Where '+@StrWhere
End
else -----------------------------------------无where条件时
Begin
Set @strSQL=@strTmp + ' where '+@ID+' '+ @strOrderBy+'('+@ID+') from (select top ' + str((@CurrentPage-1)*@PageSize)+' '+@ID+' from '+ @TblName+' '+@strOrder+' ) as tblTmp )'+' '+ @strOrder
set @recordCount='select count('+@ID+') as RecordCount from '+ @TblName
End
End
exec (@strSQL)
exec (@recordCount)
go
asp文件:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>利用存储过程分页</title>
<style>
body{font-size:12px;}
a{color:#000000;font-size:12px;text-decoration:none;}
</style>
</head>
<body>
<%
'sql server 数据库链接
dbLocal = "SY_LIKE" '服务器
dbUsername = "lee" '登录名
dbPassword = "123456" '密码
scoredatabasename="test1" '数据库
scoreconnstr = "provider = sqloledb; user id = " & dbUsername & "; password = " & dbPassword & "; initial catalog = " & scoredatabasename & "; data source = " & dbLocal & ";"
set conn = server.createobject("adodb.connection")
conn.open scoreconnstr
%>
<%
'这里给参数传值
TblName="news" '表名
id="n_id" '主键名
FldName="*" '要查询的列名(* 为所有列)
StrWhere="n_id>2" '查询条件，如：n_id>2
OrderType=1 '0为升，1为降
PageSizeX = 5 '页大小
PageNo = Clng(Request("PageNo")) '当前页
If PageNo<=0 or PageNo="" Then PageNo=1
set recom = server.createobject("adodb.command")
recom.activeconnection = conn
recom.commandtype = 4
recom.CommandTimeout = 0
recom.Prepared = true
recom.commandtext = "proc_setPage2" '存储过程名
recom.Parameters.Append recom.CreateParameter("@TblName",202,1,100,TblName)
recom.Parameters.Append recom.CreateParameter("@ID",202,1,20,id)
recom.Parameters.Append recom.CreateParameter("@FldName",202,1,100,FldName)
recom.Parameters.Append recom.CreateParameter("@StrWhere",202,1,200,StrWhere)
recom.Parameters.Append recom.CreateParameter("@OrderType",3,1,1,OrderType)
recom.Parameters.Append recom.CreateParameter("@PageSize",3,1,6,PageSizeX)
recom.Parameters.Append recom.CreateParameter("@CurrentPage",3,1,6,PageNo)
set rs = recom.execute()
if not rs.eof then
%>
<table border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse;border:1px solid #CCCCCC">
<tr>
<td width="60" align="center">ID</td>
<td width="220" align="center">标题</td>
</tr>
<%
'这里显示数据
do while not rs.eof
%>
<tr>
<td align="center"><%=rs("n_id")%></td>
<td align="center"><%=rs("n_name")%></td>
</tr>
<%
rs.movenext
loop
%>
</table>
<%
set rs = rs.NextRecordset '取得第2个记录集即总记录。
RecordCount=rs("RecordCount") '得到记录总数，
'计算总页数
if (RecordCount mod PageSizeX)=0 then
n_pageCount=RecordCount/PageSizeX
else
n_pageCount=int(RecordCount/PageSizeX)+1
end if
'上下页链接
if PageNo+1>n_pageCount then
nextHref=n_pageCount
else
nextHref=(PageNo+1)
end if
if PageNo-1>1 then
prevHref=PageNo-1
else
prevHref=1
end if
indexLink="<a href='?pageNo=1'>首页</a>"
prevLink="<a href='?pageNo="&prevHref&"'>上一页</a>"
nextLink="<a href='?pageNo="&nextHref&"'>下一页</a>"
lastLink="<a href='?pageNo="&n_pageCount&"'>末页</a>"
%>
<table cellspacing="0" cellpadding="0" width="280" style="margin-top:8px;">
<tr>
<td align="center"><%=RecordCount%>条记录 <%=indexLink%> | <%=prevLink%> <%=PageNo%>/<%=n_pageCount%> <%=nextLink%> | <%=lastLink%></td>
</tr>
</table>
<%
else
response.Write("暂无数据")
end if
set rs=nothing : set recom=nothing : Conn.Close : set Conn=nothing
%>
</body>
</html>
浙公网安备 33010602011771号