sql2000临时表分页

if exists (select * from dbo.sysobjects where id = object_id(N'#temp') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #temp
create table #temp
(
   Pager2011_ID INT IDENTITY(1, 1) PRIMARY KEY ,  --索引，分页时根据这个字段来取记录
   tableid varchar(20)

)

 Insert Into #temp(tableid)
 Select
  nationNo
 From
  tbl_dict_pub_nation
 
select * from  tbl_dict_pub_nation;
select t.*,b.* from  dbo.tbl_dict_pub_nation t inner join #temp b on t.nationNo=b.tableid

 

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

 下面一个是别人写的

/****** 对象:  StoredProcedure [dbo].[Npc_Get]    脚本日期: 05/20/2008 09:46:18 ******/


CREATE PROCEDURE [dbo].[Npc_Get]
(
 @PageIndex int,
 @PageSize int,
 @OrderBy int
)
AS

IF @PageSize = 0

 Select * From Npc

Else IF @PageSize > 0

BEGIN

 Declare @PageLowerBound int,
   @PageUpperBound int,
   @PageCount int,
   @NpcCount int

 --临时索引表--
 Create Table #PageIndex
 (
  IndexID int IDENTITY(1,1) NOT NULL,
  NpcID int
 )
 IF @OrderBy = 1
 
 Begin
 Insert Into #PageIndex(NpcID)
 Select
  NpcID
 From
  Npc
 Order By
  [Level] DESC
 End
 
 Else IF @OrderBy = 2
 Begin
 Insert Into #PageIndex(NpcID)
 Select
  NpcID
 From
  Npc
 Order By
  NpcTypeID DESC
 End

 Else IF @OrderBy = 3
 Begin
 Insert Into #PageIndex(NpcID)
 Select
  NpcID
 From
  Npc
 Order By
  AreaID DESC
 End

 --最大页码计算--
 Set @NpcCount = @@ROWCOUNT
 Set @PageCount = (@NpcCount - 1) / @PageSize + 1

 --页码范围校验--
 IF @PageIndex < 1 Set @PageIndex = 1
 IF @PageIndex >=@PageCount Set @PageIndex = @PageCount
 Set @PageLowerBound = @PageSize * (@PageIndex - 1)
 Set @PageUpperBound = @PageLowerBound + @PageSize + 1

 --返回记录--
 Select
  tS.*,
  nt.NpcTypeName,
  nl.NpcLevelName,
  nc.NpcClassName,
  a.AreaName,
  c.CampName
 From
  Npc tS
  Left Join NpcType nt on tS.NpcTypeID = nt.NpcTypeID
  Left Join NpcLevel nl on tS.NpcLevelID = nl.NpcLevelID
  Left Join NpcClass nc on tS.NpcClassID = nc.NpcClassID
  Left Join Area a on tS.AreaID = a.AreaID
  Left Join Camp c on tS.CampID = c.CampID,
  #PageIndex tPI
 Where
  tS.NpcID = tPI.NpcID AND
  tPI.IndexID > @PageLowerBound AND
  tPI.IndexID < @PageUpperBound
 Order By
  IndexID

 Select @NpcCount AS NpcCount,@PageCount AS PageCount,@@ROWCOUNT AS ReturnCount
END