分頁存儲過程(Sql Server) （表名、目前頁索引、每頁顯示行數，則傳回 資料集和總頁數）

create proc Pageproc
(
	@TableName varchar(50),	--表名
	@pagecount int,			--目前頁索引
	@pagesize int,			--每頁顯示的行數
	@zongye int output		--總行數
)
as

--擷取總行數
declare @zonghang int;
declare @getcountsql nvarchar(500)
set @getcountsql='select @a=count(*) from '[email protected]+' ' 
exec sp_executesql @getcountsql,N'@a int output',@zonghang output --把執行的結果賦給變量@zonghang


declare @strSql nvarchar(500)
set @strSql='select top '+convert(varchar,@pagesize)+' * from '[email protected]+'';
begin
	if @pagecount!=0
	begin
		set @[email protected]+'where id>(select max(id) from (select top ('[email protected]*@pagecount+') id from '[email protected]+' order by id asc) as aa)'
	end
	exec sp_executesql @strSql;
end
begin
	if @zonghang%@pagesize!=0 
	begin
		set @zongye=(@zonghang/@pagesize)+1;
	end
	else
	begin	
		set @[email protected]/@pagesize;
	end
end