谁能给一个存储过程分页的例子
谁能给一个存储过程分页的例子!!!最好是简单的.
在线等
谢谢
--动态从表中返回某些行数据...
----------------------------------------
create proc RunSelectByPageSize
@tablename nvarchar(30) , --表名
@pagesize int, --每页显示的行数
@pageindex int, --当前页数从0开始
@orderby nvarchar(50), --排序的列
@params nvarchar(300), --将要显示的列,用逗号隔开
@pagecount int out,
@where nvarchar(100) = '1=1', --where条件字符串
@order nvarchar(10) =' asc ' --排序方式
as
begin
declare @Sqlstr nvarchar(500)
declare @Rowcount int
set @Sqlstr=N'select @Rowcount= count(*) from '+@tablename +' where '+@where
exec sp_executesql @Sqlstr,N'@Rowcount int output',@Rowcount output
if @Rowcount=0 --如果没有找到对应条件则退出记录集为0;
begin
return
end
set @pagecount=Ceiling(@Rowcount*10*0.1/@pagesize)
if @pageindex<1 --如果是第一页的话...
set @Sqlstr = 'select top '+cast(@pagesize as varchar(4))+' '+ @params +' from '+@tablename+' where '+@where+ ' order by '+@orderby+' '+@order
else if @pageindex =ceiling(@Rowcount/@pagesize)
set @Sqlstr='select * from (select top '+cast((@Rowcount-@pagesize*(@pageindex)) as varchar(4))+ ' '+ @params +' from '+
@tablename+' where '+@where +' order by +'+@orderby+' desc) temptable order by '+@orderby+' '+@order
else if @pageindex >ceiling(@Rowcount/@pagesize)
begin
select @Rowcount
return
end
else
set @Sqlstr='select '+@params + ' from (select top '+cast(@pagesize as varchar(4)) +' * from '+
'( select top '+cast(@pagesize*(@pageindex+1) as nvarchar(4))+' ' +@params +' from '+@tablename +' where '+@where
+' order by '+ @orderby+ ' asc ) temptable order by '+@orderby+ ' desc ) temptable2 order by '+ @orderby+' '+@order
exec(@Sqlstr)
end
并不是最好的算法.如果你有所改进请告诉我
另外,我发现我写的这个过程如果倒序可能存在问题.谁有空,帮忙整理.谢先
[此贴子已经被作者于2007-2-4 11:37:27编辑过]
