Sql分页查询

  -- 修改了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 ( &apos; order by &apos; , @tsql ) <> 0
begin
    set @SqlSelect = replace ( substring ( @tsql , 1 , CHARINDEX ( &apos; order by &apos; , @tsql ) - 1 ), &apos; $ &apos; , &apos;&apos;&apos;&apos; ) 
    set @orderby = replace ( substring ( @tsql , CHARINDEX ( &apos; order by &apos; , @tsql ), len ( @tsql ) ), &apos; $ &apos; , &apos;&apos;&apos;&apos; )     
    set @AllowPagingSql =
        &apos; select * from (SELECT  ROW_NUMBER() OVER( &apos; + @orderby + &apos; ) AS AllowPagingId,* FROM ( &apos; +
        @SqlSelect
        + &apos; ) as table1) as table2 where AllowPagingId between &apos;
        + convert ( varchar ( 10 ),(( @pageindex - 1 ) * @PageSize + 1 )) + &apos; and &apos;
        + convert ( varchar ( 10 ), @pageindex * @PageSize )        
    exec   ( @AllowPagingSql )
end
else
begin
    set @SqlSelect = replace ( @tsql , &apos; $ &apos; , &apos;&apos;&apos;&apos; ) 
    set @orderby = &apos;&apos;
    set @AllowPagingSql =
        &apos; select *  from (SELECT  *,ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId FROM  ( select *, 1 as orderbyID from ( &apos;
        + @SqlSelect
        + &apos; )  as  tbs1 )   as Tabl1 ) as table2 where AllowPagingId between &apos;
        + convert ( varchar ( 10 ),(( @pageindex - 1 ) * @PageSize + 1 )) + &apos; and &apos;
        + convert ( varchar ( 10 ), @pageindex * @PageSize )        
        exec   ( @AllowPagingSql )
end

set @AllowPagingSql = &apos; select
case
     when count(*)% &apos; + convert ( varchar ( 10 ), @PageSize ) + &apos; =0 then count(*)/ &apos; + convert ( varchar ( 10 ), @PageSize ) + &apos;
     when count(*)% &apos; + convert ( varchar ( 10 ), @PageSize ) + &apos; <>0 then count(*)/ &apos; + convert ( varchar ( 10 ), @PageSize ) + &apos; +1
end as pageCount,count(*) as RowsCount from ( &apos; + @SqlSelect + &apos; ) as tab1 &apos;
exec   ( @AllowPagingSql )