以SQL Server2012提供的offset ..rows fetch next ..rows only为例
e.g.
表名:Tab1
----------------------------------
ID Name
1 tblAttributeGroupDetail
2 tblAttributeGroup
3 tblAttribute
.......
50 tblBRItemTypeAppliesTo
51 tblBRItemProperties
52 tblBRItem
53 tblBRBusinessRule
54 Test
--创建分页存储过程 rTabByCondition
USE [ExampleDB]
GO
if OBJECT_ID('rTabByCondition','P') is not null
drop procedure rTabByCondition
GO
create procedure [dbo].[rTabByCondition](
@PageCount int=1 --页数
,@PageSize int=10 --页显示记录数
,@Rowcount int=0 output --总记录数
)
as
set nocount on;
declare @Rows int;
select * from dbo.Tab1 order by ID offset (@PageCount-1)*@PageSize rows fetch next @PageSize rows only
set @Rows=@@ROWCOUNT
select @Rowcount=count(*) from dbo.Tab1;
return @Rows
go
declare @i int,@j int
exec @i=