SQL 分页存储过程

/* QQ：312430633 创建日期:2008-6-28 Email:[e-mail]*/CREATE PROCEDURE  [dbo].[les_AllowPaging] @pageindex int,    ----*****页码@PageSize int,     ----*****每页显示条数@tsql varchar(4000)----*****SQL语句asDeclare @SqlSelect  varchar(4000)Declare @orderby  varchar(4000)Declare @AllowPagingSql  varchar(4000) ---判断是否排序if CHARINDEX('order by',@tsql) <> 0beginset @SqlSelect=replace(substring (@tsql,1, CHARINDEX('order by',@tsql)-1),'

,'''') set @orderby=replace(substring (@tsql, CHARINDEX('order by',@tsql),len(@tsql) ),','''') set @AllowPagingSql='select * from (SELECT ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ('+@SqlSelect+') as table1) as table2 where AllowPagingId between '+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '+convert(varchar(10), @pageindex * @PageSize) exec (@AllowPagingSql)end elsebeginset @SqlSelect=replace(@tsql,','''') set @orderby='' set @AllowPagingSql='select * from (SELECT ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId ,* FROM ( select *, 1 as orderbyID from ( '+@SqlSelect+' ) as tbs1 ) as Tabl1 ) as table2 where AllowPagingId between '+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '+convert(varchar(10), @pageindex * @PageSize) exec (@AllowPagingSql)endset @AllowPagingSql='select case when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+' when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'exec (@AllowPagingSql)--exec [les_aaa] 4,15,' select * from sys.sysobjects where id>$27$ '