自各儿寫的分頁存儲過程

自己寫的分頁存儲過程

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)=''--查詢條件,不能帶whereasset nocount ondeclare @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方式beginif @OrderDesc=0--升序排列beginset @orderString=@orderFields+' asc'set @functionSring=N'>(select max('endelsebeginset @orderString=@orderFields+' desc'set @functionSring=N'<(select min('endif @pageIndex=1--第一頁beginif((@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 '+@orderStringexecute sp_executesql @sql,N'@recordPerPage int',@pageSizeendelse--非第一頁beginif((@condition='') or (@condition is null))--無條件set @wheresql=''else set @wheresql=' and '+@conditionset @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 '+@orderStringexecute sp_executesql @sql,N'@recordPerPage int,@pageNo int',@pageSize,@pageIndexendendelse--------------------------------顛倒方式beginif @orderFields!=N''--排序條件不為空set @orderString=N'order by '+@orderFieldselseset @orderString=N''if((@condition='') or (@condition is null))--無條件set @wheresql=''else set @wheresql=' where '+@condition--if @pageIndex=1--第一頁beginset @sql=N'select top(@recordPerPage) '+@getFields+N' from '+@tableName+N' '+@wheresql+N' '+@orderStringexecute sp_executesql @sql,N'@recordPerPage int',@pageSizeendelse--非第一頁beginset @versOrderString=replace(@orderFields,' asc',' _asc')--將排序依據翻轉過來set @versOrderString=replace(@versOrderString,' desc',' asc')set @versOrderString=replace(@versOrderString,' _asc',' desc')set @versOrderString=N'order by '+@versOrderStringset @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 '+@orderStringexecute sp_executesql @sql,N'@recordPerPage int,@pageNo int',@pageSize,@pageIndexendendset nocount offgo

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