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 )