自己寫的分頁存儲過程

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 @[email protected]+' asc'
set @functionSring=N'>(select max('
end
else
begin
set @[email protected]+' desc'
set @functionSring=N'<(select min('
end
if @pageIndex=1			--第一頁
begin
if((@condition='') or (@condition is null))	--無條件
set @wheresql=''
else 
set @wheresql=' where '[email protected]			--
set @sql=N'select top(@recordPerPage) '[email protected]+' from '[email protected][email protected]+N' order by '[email protected]
execute sp_executesql @sql,N'@recordPerPage int',@pageSize
end
else					--非第一頁
begin
if((@condition='') or (@condition is null))	--無條件
set @wheresql=''
else 
set @wheresql=' and '[email protected]
set @sql=N'select top(@recordPerPage) '[email protected]+' from '[email protected]+N' where '[email protected][email protected][email protected]+N') from (select top ((@pageNo-1)*@recordPerPage) '[email protected]+N' from '[email protected]+N' where '[email protected]+N' order by '[email protected]+N') as temp)'[email protected]+' order by '[email protected]
execute sp_executesql @sql,N'@recordPerPage int,@pageNo int',@pageSize,@pageIndex
end
end
else					--------------------------------顛倒方式
begin
if @orderFields!=N''	--排序條件不為空
set @orderString=N'order by '[email protected]
else
set @orderString=N''
if((@condition='') or (@condition is null))	--無條件
set @wheresql=''
else 
set @wheresql=' where '[email protected]			--
if @pageIndex=1			--第一頁
begin
set @sql=N'select top(@recordPerPage) '[email protected]+N' from '[email protected]+N' '[email protected]+N' '[email protected]
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 '[email protected]
set @sql=N'select * from (select top(@recordPerPage) * from (select top(@pageNo*@recordPerPage) '[email protected]+N' from '[email protected]+N' '[email protected]+N' '[email protected]+N') as tempTable '[email protected]+N') as tempTB '[email protected]
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'