存储过程分页（id>(max(id)) id<(min(id))）

USE [BBS]

GO

/****** Object:  StoredProcedure [dbo].[fenye2]    Script Date: 12/18/2010 13:42:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[fenye2]

(

@currentpage int, --当前页

@pagesize int,--页面有多少条记录

@key varchar(30),--表关键字（自增长）

@tablename varchar(30),--表 名字

@where varchar(50),--查询条件  等于NULL为空   有查询条件的（即不为空）不带where

@order varchar(1),-- 等于0  desc 降序  等于1 升序

@pagecount int,--有多少个页面

@str varchar(450) output--

)

as

 

begin

----------------------------------执行的SQL语句------------------------------------------------

 

declare @sql nvarchar(400),@orderby nvarchar(200)

declare @tempsqla varchar(200),@tempsqlb varchar(200)

 

 

-----------------------------------记录总数-----------------------------------------------------

 

declare @count int

 

 

-----------------------------------临时变量-----------------------------------------------------

 

 

declare @temp1 int,@temp2 int

 

--------------------------------------------------------------------------------------------------

set @tablename=' ' +@tablename+' '

 

set @key=' '+@key+'  '

 

-----------------------------------给@count赋值，声明@count是output类型-----------------------------------------------------

 

if @where='null'

 

set @sql='select @count=count(*) from '+@tablename

 

else

 

set @sql='select @count=count(*) from '+@tablename+' where '+@where

 

exec sp_executesql @sql,N'@count int out',@count out

 

---------------------------------设置升序、降序------------------------------------------------------------

 

if @order='0'

 

set @orderby=' order by '+@key+' desc'

 

else

 

set @orderby=' order by '+@key

 

 

------------------------------------总页数---------------------------------------------------

 

if(@count%@pagesize)=0

 

set @pagecount=@count/@pagesize

 

else

 

set @pagecount=@count/@pagesize+1

 

 

------------------------------判断当前页是否显示异常--------------------------------------------------------------

 

if @currentpage>@pagecount

 

set @currentpage=@pagecount

 

if @currentpage<1

 

set @currentpage=1

 

 

---------------------------------设置已经完成，开始排序-----------------------------------------------------------------------

 

 

 

---------------------------------显示第一页数据-----------------------------------------------------------------------

 

if @currentpage=1

 

begin

 

 

if @where='null'

 

set @where=' '

 

else

 

set @where=' where '+@where

 

set @sql='select top '+STR(@pagesize)+' * from '+@tablename+@where+@orderby

 

end

 

else

 

begin

 

 

 /* ---------------desc---------------------- 

       *@temp1表示前面的记录

       *@temp2表示后面的记录

       *假设一共77个记录，每次取10个。取67～58（第2页），去掉前面的57（1～57）个和后面的10个（77～66）

       */

 

if @order='0'

 

begin

 

 

set @temp1=@count-@currentpage*@pagesize  -- temp1= 57  3,44

 

 

 

set @temp2=(@currentpage-1)*@pagesize  --10  temp2=10  3,20   3  >44  <58   4  >33 <30

 

if @where='null'

 

begin

if @temp1<=0

begin

set @tempsqla='0'

 

set @tempsqlb='select MIN('+(@key)+') from (select top '+STR(@temp2)+' '+@key+' from '+@tablename+@orderby+' ) as T2'

end

else

 

begin

set @tempsqla='select MAX('+(@key)+') from (select top '+STR(@temp1)+' '+@key+' from '+@tablename+' order by '+@key+' ) as T1'

 

set @tempsqlb='select MIN('+(@key)+') from (select top '+STR(@temp2)+' '+@key+' from '+@tablename+@orderby+' ) as T2'

 

end

 

end

 

else

 

begin

if @temp1<=0

begin

set @tempsqla='0'

 

set @tempsqlb='select MIN('+(@key)+') from (select top '+STR(@temp2)+' '+@key+' from '+@tablename+' where '+@where+@orderby+' ) as T2'

end

else

begin

set @tempsqla='select MAX('+(@key)+') from (select top '+STR(@temp1)+' '+@key+' from '+@tablename+' where '+@where+' order by '+@key+' ) as T1'

 

set @tempsqlb='select MIN('+(@key)+') from (select top '+STR(@temp2)+' '+@key+' from '+@tablename+' where '+@where+@orderby+' ) as T2'

end

end

 

 

 

set @sql='select top '+STR(@pagesize)+' * from '+@tablename+' where '+@key+' <'

 

set @sql=@sql+' ( '+@tempsqlb+' ) and '

 

set @sql=@sql+@key+' > ( '+@tempsqla+' )'

 

 

 

 

 

 

if @where='null'

 

 

set @sql=@sql+@orderby

 

else

 

set @sql=@sql+' and '+@where+@orderby

 

end

 

else

 

begin

 

set @temp1=(@currentpage-1)*@pagesize

 

if @where='null'

 

set @tempsqla='select MAX('+(@key)+') from (select top '+STR(@temp1)+' '+@key+' from '+@tablename+@orderby+' ) as T3'

 

else

 

set @tempsqla='select MAX('+(@key)+') from (select top '+STR(@temp1)+' '+@key+' from '+@tablename+' where '+@where+@orderby+' ) as T3'

 

set @sql='select top '+STR(@pagesize)+' * from '+@tablename+' where '+@key+' >'

 

set @sql=@sql+' ( '+@tempsqla+' ) '

 

if @where='null'

 

set @sql=@sql+@orderby

 

else

 

set @sql=@sql+' and '+@where+@orderby

 

end

 

-------------------------------结束---------------------------------------------

end

 

set @str=@sql

 

end