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