存储过程-大数据通用高效分页（不带条件和排序）

create PROCEDURE [dbo].[GetPageDataOut]
(
@tn nvarchar(30),--表名称
@idn nvarchar(20),--表主键名称
@pi int = 1,--当前页数 
@ps  int = 7,--每页大小
@rc int output,--总行数(传出参数)
@pc int output--总页数(传出参数)
)
AS
DECLARE @sql NVARCHAR(225),@sqlCount NVARCHAR(225)
SET @sqlCount = 'SELECT @rc=COUNT(['+@idn+']),@pc=CEILING((COUNT('+@idn+')+0.0)/'+ CAST(@ps AS VARCHAR)+') FROM ' + @tn
EXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@pc INT OUTPUT',@rc OUTPUT,@pc OUTPUT

IF @pi = 1
BEGIN
	SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn
EXEC(@sql)
END
ELSE
BEGIN
	SET NOCOUNT ON
		DECLARE @PageLowerBound INT,@StartID INT
		SET @PageLowerBound = @ps * (@pi-1)
		IF @PageLowerBound<1
			SET @PageLowerBound=1
		SET ROWCOUNT @PageLowerBound
		SET @sql=N'SELECT  @StartID = ['+@idn+'] FROM '+@tn
		print '@sql='+@sql	
	    EXEC sp_executesql @sql,N'@StartID int output',@StartID output
		SET ROWCOUNT 0
		SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn+' WHERE ['+@idn+']>'+ str(@StartID)
		EXEC(@sql)
	SET NOCOUNT OFF
END

--测试语句
declare @rc int,@pc int
exec GetPageDataOut 'Ams_Area','ar_id',2,15,@rc output,@pc output
select @rc,@pc