1.返回記錄集總數存儲過程：
CREATE procedure dbo.recordCount
(
@TableName nvarchar(100),
@strWhere nvarchar(500),
@count int output
)
as
declare @sqlStr nvarchar(1000)
if @strWhere != ''''
set @sqlStr = N''select @COUNT = count(id) from ''+ @TableName +'' where 1=1 '' + @strWhere
else
set @sqlStr = N''select @COUNT = count(id) from ''+ @TableName
exec sp_executesql @sqlStr,N''@count int output'',@count output
GO
2.sql2000存儲過程分頁，這個也是在網上找的，有錯誤，自己修改過來了。
CREATE PROCEDURE dbo.sp_pagevIEw
@tablename varchar(200) , --表名
@strGetFIElds varchar(200), --查詢列名
@PageIndex int , --頁碼
@pageSize int, --頁面大小
@strWhere varchar(100) , --查詢條件
@strOrder varchar(100) , --排序列名
@intOrder bit --排序類型 1為升序
AS
begin
declare @strSql varchar(500) --主語句
declare @strTemp varchar(100) --臨時變量
declare @strOrders varchar(50) --排序語句
declare @table varchar(70)
if @intOrder = 0
begin
--為0是升序
set @strTemp = ''>(select max''
set @strOrders = '' order by ''+@strOrder+'' asc ''
end
else
begin
--否則為降序
set @strTemp = ''<(select min''
set @strOrders = '' order by ''+@strOrder+'' desc ''
end
if @PageIndex =1 --第一頁直接讀出紀錄
begin
if @strWhere = ''''
begin
set @strSql = ''select top ''+str(@pageSize)+'' ''+@strGetFIElds+'' from ''+@tablename+'' ''+@strOrders
end
else
begin
set @strSql = ''select top ''+str(@pageSize)+'' ''+@strGetFIElds+ '' from ''+@tablename+'' where ''+@strWhere+'' ''+@strOrders
end
end
else
begin
set @strSql = ''select top''+str(@pageSize)+'' ''+@strGetFIElds+'' from ''+@tablename+'' where ''+@strOrder+'' ''+@strTemp+'' (''+@strOrder+'')''
+'' from (select top ''+str((@pageIndex-1)*@pageSize)+'' ''+@strGetFIElds+'' from ''+@tablename+ '' ''+@strOrders+ '') as tempTable ) ''+@strOrders
if @strWhere != '' ''
begin
set @strSql = ''select top ''+str(@pageSize)+ '' ''+@strGetFIElds+'' from ''+@tablename+ '' where ''+@strOrder+ '' ''+@strTemp+'' (''+@strOrder+'') ''
+'' from(select top ''+str((@pageIndex-1)*@pageSize)+'' ''+@strGetFIElds+'' from ''+@tablename+'' where ''+@strWhere+'' '' +@strOrders+'') as tempTable) and ''+@strWhere+'' ''+@strOrders
end
end
exec(@strSql)
end
GO
3.asp調用頁面：list.ASP
<!--#include file="conn.ASP"-->
<!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" />
<script language="Javascript">
function checkpage()
{
if(isNaN(document.fenye.page.value))
{
alert("跳轉頁碼請輸入數字！");
document.fenye.page.focus();
document.fenye.page.value='''';
return false;
}
}
</script>
<title>存儲分頁</title>
<%
dim TableName,Page,TotalRs,PageNum,TotalPage,SearchChar,strGetFIElds,strOrder
''存儲過程參數
TableName="cTongJi_product"
strGetFIElds=" id,ip,into_time "''字段開始結束加上空格
strOrder="id"
PageNum=30
''搜索關鍵字
key=request("key")
''按什麼字段搜索
kind=request("kind")
''分頁參數
page=cint(request("page"))
trs=request("trs")
tpa=request("tpa")
''查詢條件判斷
if key<>"" then
SearchChar=" and "& kind &" like ''%"&key&"%''"
SearchChar1=" "& kind &" like ''''%"&key&"%''''"''書寫注意與存儲過程中的where關鍵字間的間距，如果多條件，後面也要有間距
else
SearchChar=""
SearchChar1=""
''response.Write SearchChar1
''response.End()
end if
''返回總記錄數
Set MyComm=Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection=conn
''MyConStr是數據庫連接字串
MyComm.CommandText="recordCount"
''指定存儲過程名
MyComm.CommandType=4
''表明這是一個存儲過程
MyComm.Prepared=true
''要求將SQL命令先行編譯
''聲明參數
MyComm.Parameters.append MyComm.CreateParameter( "@TableName",200,1,500,
<body>
<table width="800" height="46" border="1" align="center">
<form action="list.ASP" method="post">
<tr>
<td colspan="3">關鍵字：<input type="text" name="key" /> <input type="radio" value="how" name="kind" />功能<input type="radio" checked="checked" value="what" name="kind" />產品<input type="radio" value="ip" name="kind" />廠家
<input type="submit" name="Submit" value="搜 索" />
</td>
</tr></form>
</table>
<table width="800" height="46" border="1" align="center">
<tr>
<td height="35" align="center"><strong>代理區域</strong></td>
<td align="center"><strong>要求代理品種 </strong></td>
<td height="35" align="center"><strong>性質</strong></td>
<td height="35" align="center"><strong>發布時間</strong></td>
</tr>
<%
do while not rs.eof
%>
<tr>
<td height="30"><%=rs("id")%></td>
<td height="30"><%=rs("ip")%></td>
<td height="30"><%=rs("into_time")%></td>
<td height="30"> </td>
</tr>
<%
rs.movenext
loop
%>
</table>
<table width="800" height="46" border="1" align="center">
<form action="list.ASP" method="post" name="fenye" onsubmit="return checkpage()">
<tr>
<td colspan="3" align="center">共<%=TotalRs%>條 <%=PageNum%>條/頁 當前第<%=Page%>/<%=TotalPage%>頁
<%if page=1 then
response.Write" 首頁 上一頁 <a href=''list.asp?page="&page+1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"''>下一頁</a> <a href=''list.ASP?page="&TotalPage&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"''>末頁</a>"
end if%>
<%if page>1 and page<TotalPage then
response.Write"<a href=''list.asp?page=1&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"''>首頁</a> <a href=''list.asp?page="&page-1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"''>上一頁</a> <a href=''list.ASP?page="&page+1&"&key="&key&"&kind="&kind&"&trs="&trs&
"&tpa="&tpa&"''>下一頁</a> <a href=''list.ASP?page="&TotalPage&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"''>末頁</a>"
