ASP分页存储过程

 cr�ate proc AbsolutePages
(
@PageSize int=0 ,             --每页显示的记录数
    @PageCurrent int= ,           --当前要显示的页号
    @FdName varchar(00)='' ,      --主键名或者标识列名
    @Sel�ctStr varchar(2000)='',   --sel�ct子句，不包含sel�ct关键字,如：*或者id,UserId,UserName等。
    @FromStr varchar(000)='',     --from子句，不包含from关键子，如：myTable或者myTable,yourTable
    @Wh�reStr varchar(2000)='',    --wh�re子句，不包含wh�re关键字，如空的，或者 id>2 等
    @OrderByStr varchar(000)=''  --order by 子句，不包含order by 子句 ，如id desc,UserId asc 等
)
as
------------------定义局部变量------------------
declare @Id varchar(20),@Id2 varchar(20) --开始和结束的记录号
declare @OrderBySqls varchar(000)        --order by 子句
declare @Wh�reSqls varchar(2000)          --wh�re 子句
declare @Sqls nvarchar(4000)              --最终组合成的Sqls语句
declare @TmpStr varchar(2000)             --临时语句
------------
if @Wh&#0;reStr <> ''
set @Wh&#0;reSqls = ' wh&#0;re ('+@Wh&#0;reStr ')'
else
set @Wh&#0;reSqls = ''
------------
if @OrderByStr <> ''
set @OrderBySqls = ' order by ' @OrderByStr
else
set @OrderBySqls = ''
------------
set @TmpStr = @Wh&#0;reSqls
------------------如果显示第一页，可以直接用top来完成------------------
if @PageCurrent <=
begin
sel&#0;ct @Id = convert(varchar(20),@PageSize)
set @Sqls = 'sel&#0;ct top '   @Id   ' '   @Sel&#0;ctStr   ' from '   @FromStr   @Wh&#0;reSqls   @OrderBySqls
exec (@Sqls)
end
else
begin
sel&#0;ct @Id=convert(varchar(20),@PageSize),@Id2=convert(varchar(20),(@PageCurrent-)*@PageSize)
if @Wh&#0;reSqls <> ''
set @Wh&#0;reSqls = @Wh&#0;reSqls   ' and ('   @FdName ' not in(sel&#0;ct top ' @Id2 ' ' @FdName ' from ' @FromStr @Wh&#0;reSqls @OrderBySqls '))'
else
set @Wh&#0;reSqls = ' wh&#0;re '   @FdName ' not in(sel&#0;ct top ' @Id2 ' ' @FdName ' from ' @FromStr @Wh&#0;reSqls @OrderBySqls ')'
----------
 set @Sqls = 'sel&#0;ct top ' @Id  ' '  @Sel&#0;ctStr ' from ' @FromStr @Wh&#0;reSqls @OrderBySqls
exec (@Sqls)
end
print @Sqls
return
GO

 cr�ate proc AbsolutePageEx
(
@PageSize int=0,             --每页要显示的记录条数
@PageCurrent int=,           --要显示第几页
@table nvarchar(4000),        --表名、视图名、记录集、查询结果
@Fields nvarchar (4000)='',   --要显示的字段列表(如果查询结果有标识字段,必须指定此值,且不包含标识字段)
@order nvarchar (000)=''     --排序字段列表
)
as
begin
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 declare @Id varchar(20),@Id2 varchar(20) --开始和结束的记录号
 declare @Obj_ID int --对象id
 --表中有复合主键的处理
 declare @strfd nvarchar(2000) --复合主键列表
 declare @strjoin nvarchar(4000) --连接字段
 declare @strwh�re nvarchar(2000) --查询条件
 --格式化表、字段、排序字段的参数形式
 sel�ct @Obj_ID=object_id(@table)
,@Fields=case isnull(@Fields,'') when '' then ' *' else ' '+@Fields end
,@order=case isnull(@order,'') when '' then '' else ' order by ' @order end
,@table=case when @Obj_ID is not null then ' ' @table else ' (' @table ') a' end
------如果要显示第一页，可以直接用top来完成------
 if @PageCurrent=
begin
sel�ct @Id=cast(@PageSize as varchar(20))
exec('sel�ct top ' @Id @Fields ' from ' @table @order)
return
end
------如果是表,则检查表中是否有标识更或主键------
 if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=
begin
set @Id=cast(@PageSize as varchar(20))
set @Id2=cast((@PageCurrent-)*@PageSize as varchar(20))
sel�ct @FdName=name from syscolumns wh�re id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
  begin
if not exists(sel�ct  from sysobjects wh�re parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理

sel�ct @FdName=name from syscolumns wh�re id=@Obj_ID and colid in(
sel�ct colid from sysindexkeys wh�re @Obj_ID=id and indid in(
sel�ct indid from sysindexes wh�re @Obj_ID=id and name in(
sel�ct name from sysobjects wh�re xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount> --检查表中的主键是否为复合主键
   begin
sel�ct @strfd='',@strjoin='',@strwh�re=''
sel�ct @strfd=@strfd ',[' name ']'
,@strjoin=@strjoin ' and a.[' name ']=b.[' name ']'
,@strwh�re=@strwh�re ' and b.[' name '] is null'
from syscolumns wh�re id=@Obj_ID and colid in(
sel�ct colid from sysindexkeys wh�re @Obj_ID=id and indid in(
sel�ct indid from sysindexes wh�re @Obj_ID=id and name in(
sel�ct name from sysobjects wh�re xtype='PK' and parent_obj=@Obj_ID
)))
sel�ct @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwh�re=substring(@strwh�re,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
 lbuseidentity:
exec('sel�ct top ' @Id @Fields ' from ' @table
' wh�re ' @FdName ' not in(sel�ct top '
@Id2 ' ' @FdName ' from ' @table @order
')' @order
)
return
/*--表中有复合主键的处理方法--*/
 lbusepk:
exec('sel�ct ' @Fields ' from(sel�ct top ' @Id ' a.* from
(sel�ct top 00 percent * from ' @table @order ') a
left join (sel�ct top ' @Id2 ' ' @strfd '
from ' @table @order ') b on ' @strjoin '
wh�re ' @strwh�re ') a'
)
return
/*--用临时表处理的方法--*/
 lbusetemp:
sel�ct @FdName='[ID_' cast(newid() as varchar(40)) ']'
,@Id=cast(@PageSize*(@PageCurrent-) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent- as varchar(20))
exec('sel�ct ' @FdName '=identity(int,0,),' @Fields '
into #tb from ' @table @order '
sel�ct ' @Fields ' from #tb wh�re ' @FdName ' between '
@Id ' and ' @Id2
)
end
GO