sql分页查询

rikii

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

wcb820710

学习中