自己写了这么一个存储过程，是SQL SERVER
CREATE procedure [dbo].[firstsplit](
@tableName varchar(50), --数据表名称
@tableIndex varchar(20), --表主键
@pageSize int, --每页大小
@pageIndex int, --第几页
@recordNum int output, --返回总共记录数
@pageNum int output, --返回总页数
@whereStr varchar(1000) --查询条件
)
as
set nocount on
declare @sql varchar(1000)
begin
if @tableName is null return
set @sql='select b.* from ('
set @sql=@sql+'select top '+CAST(@pagesize AS varchar)+' a.* from('
set @sql=@sql+' select top '+Cast((@pageSize*@pageIndex)as varchar)+' * from '+@tableName+' where ('+@whereStr+') order by '+@tableIndex+' desc ) as a order by mesId asc) as b order by mesId desc'
exec(@sql)
set @sql='select count('+@tableIndex+') from '+@tableName+' where '+@whereStr
exec(@sql)
set @recordNum=@@rowcount
if @recordNum % @pageSize=0
set @pageNum=ceiling(@recordNum/@pagesize)
else
set @pageNum=ceiling(@recordNum/@pageSize)+1
end
GO
然后调用如下：
<%dim page
page=request.QueryString("page")
if page="" then page=1
set cmd=server.CreateObject("adodb.command")
with cmd
.activeConnection=conn
.commandType=4
.commandText="firstsplit"
.prepared=true
.parameters.append .createParameter("@tableName",200,1,20,"message")
.parameters.append .createParameter("@tableIndex",200,1,10,"MesId")
.parameters.append .createParameter("@pageSize",3,1,,10)
.parameters.append .createParameter("@pageIndex",3,1,,page)
.parameters.append .createParameter("@recordNum",3,2)
.parameters.append .createParameter("@pageNum",3,2)
.parameters.append .createParameter("@whereStr",200,1,200,"mesEmail='yutaozj@163.com'")
set rs=.execute
end with
rs.close
response.write "<br>总共记录数:"&cmd(4)
response.write "<br>总页数："&cmd(5)
response.write "<br>当前页："&page&"<br>"
rs.open
do while not rs.eof
response.write rs("mesId")&"<br>"
rs.movenext
loop
rs.close()
set rs=nothing
set cmd=nothing
%>
可是返回总记录数是1，这是为什么啊？帮忙修正下啊
