SQLSERVER下完成分页

曾经在写过一篇Sql Server2005下的分页的实现谈论了如何利用2005的新特性row_number实现数据分页，但其中仅仅是写了一些sql语句写法，其实还可以将他们写成存储过程或者函数。今天在网上看到一篇讨论分页的文章，原以为已经不是问题的问题，其实还很热，不免花几分写一个例子分享，也希望大伙儿拍砖。 例子如下：

create table County
(
	id int identity(1,1) not null primary key,
	CountID int,
	CountyName nvarchar(10)    
)
--注意算sql中的循环
declare @countId int,@CountName nvarchar(10), @count nvarchar(10)
set @countId=1
set @CountName='中华县'
set @count=@CountName
while(@countId<2000)
begin
insert into County(CountID,CountyName)values(@countId+1,@count)
set @countId=@countId+1
set @count=@CountName+cast(@countId as nvarchar(10))
end 
go

create procedure sp_Paging_GetCounty
@startIndex int,
@endIndex int
as
with table_temp as (select row_number() over(order by id) as rowIndex,* from County)
select * from table_temp where rowIndex between @startIndex and @endIndex
go
--通过开始行和结束行的索引获取结果集的函数
create function ft_Paging_GetCounty(@startIndex int,@endIndex int)
returns table
as
return(with table_temp as (select row_number() over(order by id) as rowIndex,* from County)
select * from table_temp where rowIndex between @startIndex and @endIndex)
go
--获取所有符合查询条件的结果集的总行数

select count(*) from County
go
--执行以下操作时

exec sp_Paging_GetCounty 1,12
go
select * from ft_Paging_GetCounty(1,5)
go

 有感兴趣的可以直接粘下来试试。

 

转载于:https://my.oschina.net/haifengwang/blog/6937