实现一个简单的SQLServer2005分页存储过程

其实这个储存过程也可以不写，因为，row_number的功能，已经能让分页功能实现起来很简单了，但是出于对重复coding的简化，以及对我以前写的一个oracle的分页存储过程的 翻版，所以决定还是写一下。

实现起来还是跟Oracle的分页存储过程的思路是一样的。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Paul_Huang>
-- Create date: <2007-12-31>
-- Description: <using row_number function for paging>
--    <@PageSQLText must be contianed column that name is 'rowid'>
-- =============================================
Create PROCEDURE Paging
 
 @PageSQLText nvarchar(4000),
 @PageIndex int,
 @PageSize int,
 @PageCount int out,
 @ResultCount int out
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 declare @tmpSql nvarchar(4000),@height int,@low int

 set @tmpSql = N'select @ResultCount = count(rowid) from ( ' + @PageSQLText + ' ) t ' 
 exec sp_executesql @tmpSql,N'@ResultCount int output',@ResultCount output

 if (@ResultCount%@PageSize>0)
  begin
   select @PageCount=@ResultCount/@PageSize + 1
  end
 else
  begin
   select @PageCount=@ResultCount/@PageSize
  end

 set @height = (@PageIndex + 1) * @PageSize
 set @low = @PageIndex * @PageSize + 1
 set @tmpSql = N'select * from (' + @PageSQLText + ' ) t where t.rowid between ' + cast(@low as varchar(4))+ ' and ' + cast(@height as varchar(4))

 exec (@tmpSql)

END
GO