文件名称：高效率 分页
文件大小：1KB
文件格式：TXT
更新时间：2011-07-16 08:34:54
分页 存储过程 实用 简单
/* QQ312430633 */
CREATE PROCEDURE [dbo].[les_AllowPaging]
@pageindex int,
@PageSize int,
@tsql varchar(4000)
as
Declare @AllowPagingSql varchar(4000)
set @AllowPagingSql=
'select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId, * FROM ( select *, 1 as orderbyID from ( '+@tsql +' ) as tbs1 )
as Tabl1
) as table2
where AllowPagingId between '
+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
+convert(varchar(10), @pageindex * @PageSize)
set @AllowPagingSql= replace( @AllowPagingSql,'$','''')
exec (@AllowPagingSql)
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 ('+replace(@tsql,'$','''')+') as tab1'
exec (@AllowPagingSql)
/*
exec [les_AllowPaging] 15,100,' select * from sys.sysobjects '
exec [les_AllowPaging] 15,100,' select top 100 percent * from sys.sysobjects order by id'
ORDER BY 需要 percent
传入语句中存在分号需要先把传入语句格式化.
C# 例
string SqlStr = " select * from sys.sysobjects where name like ''"；
SqlStr=SqlStr.Replace("'", "$")；
*/