/* QQ312430633 创建日期:2008-06-25 */ 
--修改了ORDER BY 需要 percent 与 ORDERBY 失效的BUG--
---注意'roder by'用一个空格间隔

Create PROCEDURE  [dbo].[les_AllowPaging] 
@pageindex int,    ----*****页码
@PageSize int,     ----*****每页显示条数
@tsql varchar(4000)----*****SQL语句
as

Declare @SqlSelect  varchar(4000)
Declare @orderby  varchar(4000)
Declare @AllowPagingSql  varchar(4000) 

---判断是否排序
if CHARINDEX('order by',@tsql) <> 0
begin
    set @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 
else
begin
    set @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)
end

set @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)+'+1
end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'
exec  (@AllowPagingSql)