if exists (select name from sysobjects where name='proc_page' and type='p')   
drop procedure proc_page   
go  

create procedure proc_page
@tableName nvarchar(30),	--表名
@orderFields nvarchar(30),	--排序字段,top max方式只能有一個字段,顛倒方式可以有多個字段,但是不能省略asc
@OrderType int=2,			--1,top max 方式,2.顛倒方式
@OrderDesc bit=0,			--0升序,1降序,在顛倒方式中不需要
@getFields nvarchar(30)=N'*', --將要查詢顯示的列
@pageSize int=10,			--每頁項目數
@pageIndex int,				--第幾頁
@condition nvarchar(120)=''	--查詢條件,不能帶where
as
set nocount on
declare @sql nvarchar(600)			--sql拼合語句
declare @wheresql nvarchar(130)		--條件語句
declare @orderString nvarchar(60)	--排序語句
declare @versOrderString nvarchar(60)--反響排序排序語句
declare @functionSring nvarchar(20)	--
if @OrderType=1			--------------------------------top max方式
begin
if @OrderDesc=0			--升序排列
begin
set @orderString=@orderFields+' asc'
set @functionSring=N'>(select max('
end
else
begin
set @orderString=@orderFields+' desc'
set @functionSring=N'<(select min('
end
if @pageIndex=1			--第一頁
begin
if((@condition='') or (@condition is null))	--無條件
set @wheresql=''
else 
set @wheresql=' where '+@condition			--
set @sql=N'select top(@recordPerPage) '+@getFields+' from '+@tableName+@wheresql+N' order by '+@orderString
execute sp_executesql @sql,N'@recordPerPage int',@pageSize
end
else					--非第一頁
begin
if((@condition='') or (@condition is null))	--無條件
set @wheresql=''
else 
set @wheresql=' and '+@condition
set @sql=N'select top(@recordPerPage) '+@getFields+' from '+@tableName+N' where '+@orderFields+@functionSring+@orderFields+N') from (select top ((@pageNo-1)*@recordPerPage) '+@orderFields+N' from '+@tableName+N' where '+@condition+N' order by '+@orderString+N') as temp)'+@wheresql+' order by '+@orderString
execute sp_executesql @sql,N'@recordPerPage int,@pageNo int',@pageSize,@pageIndex
end
end
else					--------------------------------顛倒方式
begin
if @orderFields!=N''	--排序條件不為空
set @orderString=N'order by '+@orderFields
else
set @orderString=N''
if((@condition='') or (@condition is null))	--無條件
set @wheresql=''
else 
set @wheresql=' where '+@condition			--
if @pageIndex=1			--第一頁
begin
set @sql=N'select top(@recordPerPage) '+@getFields+N' from '+@tableName+N' '+@wheresql+N' '+@orderString
execute sp_executesql @sql,N'@recordPerPage int',@pageSize
end
else					--非第一頁
begin
set @versOrderString=replace(@orderFields,' asc',' _asc')	--將排序依據翻轉過來
set @versOrderString=replace(@versOrderString,' desc',' asc')
set @versOrderString=replace(@versOrderString,' _asc',' desc')
set @versOrderString=N'order by '+@versOrderString
set @sql=N'select * from (select top(@recordPerPage) * from (select top(@pageNo*@recordPerPage) '+@getFields+N' from '+@tableName+N' '+@wheresql+N' '+@orderString+N') as tempTable '+@versOrderString+N') as tempTB '+@orderString
execute sp_executesql @sql,N'@recordPerPage int,@pageNo int',@pageSize,@pageIndex
end
end
set nocount off
go

use abc 
execute proc_page N'tb',N'b',1,1,N'*',5,2,N'b<50'