其实这个储存过程也可以不写,因为,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