-----------------------分页的存储过程------------------------
drop PROCEDURE dbo.spAll_ReturnRows
CREATE PROCEDURE dbo.spAll_ReturnRows
(
@SQL nVARCHAR(4000), --select子句，不包含select关键字,如：*或者
@Page int, --当前要显示的页号
@CurrentPageRow int, --每页显示的记录数
@ID VARCHAR(255), --主键名或者标识列名
@Sort VARCHAR(255), --order by 子句，不包含order by 子句 ，如id desc,UserId asc 等
@WhereStr VARCHAR(255)--where子句，不包含where关键字，如空的，或者 id>2 等
)
AS
DECLARE @Str nVARCHAR(4000) --最终组合成的Sqls语句
DECLARE @WhereSqls VARCHAR(255)--where 子句
if @WhereStr <> ''
set @WhereSqls = ' ('+ @WhereStr +') and '
else
set @WhereSqls = ''
SET @Str='SELECT TOP '+CAST(@CurrentPageRow AS VARCHAR(20))+' * FROM ('+@SQL+') a WHERE '+ @WhereSqls +' a.'+@ID+' NOT IN
(SELECT TOP '+CAST((@CurrentPageRow*@Page) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') b ORDER BY b.'+@Sort+') ORDER BY a.'+@Sort
EXEC sp_ExecuteSql @Str
spAll_ReturnRows 'SELECT * FROM jobs',10,10,'[job_ID]','[job_ID]'
SELECT * FROM authors c where c.min_lvl=75
SELECT top 5 * FROM jobs a where min_lvl>25 and a.job_id not in (select top 5 job_id from jobs b order by b.job_id)
--------------------------ASP页面---------------------
<%
if not isempty(request("page")) then
currentPage=cint(request("page"))
else
currentPage=1
end if
MaxPerPage=5 '###每页显示条数
set con=server.createObject("adodb.connection")
con.open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=."
set rs1=con.execute("select count(*) from employee") '获取总记录数
totalPut=rs1(0)
mpage=Round(cint(rs1(0))/MaxPerPage) '得到总页数
rs1.close
set rs1=nothing
set com=server.createobject("adodb.command")
com.ActiveConnection=con
com.CommandText="spAll_ReturnRows "
com.CommandType=4
com.Parameters.Append com.createParameter("@sql",202,1, 4000, "SELECT * FROM employee")
com.Parameters.Append com.createParameter("@page",4,1, 4, currentPage-1)
com.Parameters.Append com.createParameter("@CurrentPageRow",4,1, 4, MaxPerPage)
com.Parameters.Append com.createParameter("@ID",8,1, 20, "emp_id")
com.Parameters.Append com.createParameter("@Sort",8,1, 20, "emp_id")
set rs = com.Execute
do while Not rs.EOF
Response.Write rs(0) & "--" & rs(1)& "<br>"
rs.MoveNext
loop
%>
<%
call page()
sub page()%> 页次：<b><font color="#FF0000"><%=currentPage%></font>/<%=mpage%></b>,每页<b><%=MaxPerPage%></b>个,当前记录<b><%=totalPut%></b>个
<%
pageno=currentPage
if isempty(pageno) or cint(pageno)<1 or cint(pageno)>mpage then
pageno=1
end if
%>
[ <%if cint(pageno)>1 then%><a href=1.asp?k=<%=request("k")%>><%end if%><<-</a> <%if cint(pageno)>1 then%><a
href=1.asp?page=<%=pageno-1%>><%end if%><-</a><%
pp=cint(pageno)-3
if pp<1 then
pp=1
end if
for pno=pp to mpage
p=p+1
if pno=cint(pageno) then%> <font color="#FF0000"><%=pno%></font><%else%> <a href=1.asp?page=<%=pno%>><%=pno%></a><%end if%>
<%
if p>=7 then exit for
next%> <%if cint(pageno)< mpage then%> <a href=1.asp?page=<%=pageno+1%>><%end if%>-></a> <%if cint(pageno)< mpage then%><a
href=1.asp?page=<%=mpage%>><%end if%>->></a> ]<%
end sub%><%
rs.close
Con.close
set rs=nothing
set con=nothing
%>
|
|
