自己的分存程
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 offgouse abc execute proc_page N'tb',N'b',1,1,N'*',5,2,N'b<50'