SQL2005 新增函数Row_number() 存储过程分页有大用

Row_number()为SQL2005新增,相对SQL2000下的select top x ....where id not in()........效果好不少!

---------判断是否有这个存储过程名称，有就删除
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PR_Page_Rownumber')
 BEGIN
  DROP  Procedure  PR_Page_Rownumber
 END
----------end

----------新建存储过程

GO

create procedure PR_Page_Rownumber --利用SQL 2005中的Row_number()  SQL2000没有该函数
( 
    @tableName varchar(50),
    @pageIndex int, 
    @pageSize int,
    @sort varchar(50)
) 
as 
    declare @timediff datetime 
begin 
set nocount on; 
    select @timediff=getdate() 
    select * from (select *,Row_number() over(order by @sort ) as IDRank from @tableName) as IDWithRowNumber where IDRank>(@pageSize*(@pageIndex-1)) and IDRank<=@pageSize*@pageIndex
    select datediff(ms,@timediff,getdate()) as 耗时 
set nocount off; 
end

GO

 

测试没有问题!