求助:关于SQL分页问题,使用动态存储过程,不知如何返回记录总数
CREATE procedure PagingPaging
(
@pagesize int,
@pageindex int,
@recordcount int OUT,
@pagecount int OUT,
@selectID NVarChar(20),
@selectTable NVarChar(20),
@where NVarChar(1000),
@where2 NVarChar(200)
)
as
declare @sql NVarChar(2000)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=@pageindex*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid int,oid int)
set rowcount @PageUpperBound
set @sql = 'insert into #pageindex(nid,oid) select ' + @selectID +' from ' + @selectTable + ' where ' + @where + ' ,#pageindex p where ' + @where2 + ' and p.id>' + Str(@PageLowerBound) +' and p.id<= ' + Str(@PageUpperBound) +' order by p.id'
exec(@sql)
set @recordcount = @@rowcount
set @pagecount = CEILING(@recordcount * 1.0 / @pagesize)
GO
---------------------------------------------------
语句中的@recordcount即是要返回的记录总数,不过按照现在的写法它只能返回该分页的记录数。我想实现例如
set @sql = 'select @recordcount = count(' + @selectID +') from ' + @selectTable + ' where ' + @where
exec(@sql)
但是好像一个存储过程里面sql只能运行一次。。
希望高手赐教