存储过程-简单通用分页

create PROCEDURE [dbo].[GetPageDataOutRowNumber](@tn nvarchar(30),--表名称@idn nvarchar(20),--表主键名称@pi int = 1,--当前页数 @ps int = 7,--每页大小 @wh nvarchar(255) = ,--wehre查询条件@oby nvarchar(255) = ,--orderby 排序@rc int output,--

create PROCEDURE [dbo].[GetPageDataOutRowNumber]
(
@tn nvarchar(30),--表名称
@idn nvarchar(20),--表主键名称
@pi int = 1,--当前页数 
@ps  int = 7,--每页大小 
@wh nvarchar(255) = &＃39;&＃39;,--wehre查询条件
@oby nvarchar(255) = &＃39;&＃39;,--orderby 排序
@rc int output,--总行数(传出参数)
@pc int output--总页数(传出参数)
)
AS
DECLARE @sql NVARCHAR(225)=&＃39;&＃39;,@sqlCount NVARCHAR(225)=&＃39;&＃39;
--1.计算总行数和总页数
SET @sqlCount = &＃39;SELECT @rc=COUNT([&＃39;+@idn+&＃39;]),@pc=CEILING((COUNT(&＃39;+@idn+&＃39;)+0.0)/&＃39;+ CAST(@ps AS VARCHAR)+&＃39;) FROM &＃39; + @tn
IF LEN(@wh)>1
	set @sqlCount=@sqlCount+&＃39; WHERE &＃39;+@wh
print @sqlCount
EXEC SP_EXECUTESQL @sqlCount,N&＃39;@rc INT OUTPUT,@pc INT OUTPUT&＃39;,@rc OUTPUT,@pc OUTPUT
--2.分页
--2.1如果是第一页，则直接查询
IF @pi = 1
BEGIN
	SET @sql=&＃39;SELECT TOP &＃39;+str(@ps) +&＃39; * FROM &＃39;+@tn
	IF LEN(@wh)>1
		set @sql=@sql+&＃39; WHERE &＃39;+@wh
	IF LEN(@oby)>1
			SET @sql=@sql+&＃39; order by &＃39; +@oby
EXEC(@sql)
END
ELSE--2.2如果不是第一页，则拼接查询语句
BEGIN
	SET NOCOUNT ON
	SET @sql=&＃39;SELECT * FROM (select row_number() over(order by &＃39;
	IF LEN(@oby)>1
		set @sql=@sql + @oby+&＃39;) as rowNum,* from &＃39;+@tn
	else
		set @sql=@sql + @idn+&＃39;) as rowNum,* from &＃39;+@tn
	IF LEN(@wh)>1
		set @sql=@sql+&＃39; where &＃39;+@wh
	set @sql=@sql+&＃39;)as temp where rowNum>&＃39;+str(@ps * (@pi-1))+&＃39; and rowNum<=&＃39;+str(@ps*@pi)
	print @sql
	EXEC(@sql)
	SET NOCOUNT OFF
END

--测试语句
declare @rc int,@pc int
exec [GetPageDataOutRowNumber] &＃39;Ams_Area&＃39;,&＃39;ar_id&＃39;,2,5,&＃39;&＃39;,&＃39; ar_id desc&＃39;,@rc output,@pc output
select @rc,@pc