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 )
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值