EXEC(
'SET ROWCOUNT 0'+'
Select count(1) as con FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup
) 我直接用sqlRst.getInt("con");
它会提示"Invalid column name: con"
要怎样调用它呢?
'SET ROWCOUNT 0'+'
Select count(1) as con FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup
) 我直接用sqlRst.getInt("con");
它会提示"Invalid column name: con"
要怎样调用它呢?
如果还不行 参看一下 http://blog.csdn.net/caoyinghui1986/archive/2008/04/04/2251390.aspx
drop procedure PagePROC
go
create procedure dbo.PagePROC --要修改过程把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
Endexec (@strSQL)
exec (@recordCount)
go
exec (@strSQL)
exec (@recordCount) 所以返回的应有两个记录集,那么我第一个就按平常一样调用就可以了,但第一个呢?JSP里,怎样表示第二个记录集的开始呢?如果是asp就可以这样写:
set rs = rs.NextRecordset '取得第2个记录集即总记录。
RecordCount=clng(rs("RecordCount")) '得到记录总数,
在JSP怎样写呢?
exec (@recordCount)