sql 2000 分页

create PROCEDURE [dbo].[Proc_GetPageList]

( @Tables varchar(1000), --表名 @PK varchar(100), --主键 @Fields varchar(1000) = '*', --查询的字段@SortField varchar(200) = NULL, --排序的字段@SortType varchar(10)= 'ASC', --排序方式 @PageIndex int = 1, --页码索引 @PageSize int = 10, --页码大小 @Filter varchar(1000) = NULL, --过滤条件@PageCount int = 1 output, ----查询结果分页后的总页数 @RecordCounts int = 1 output ----查询到的记录数

)

AS

SET NOCOUNT ON

declare @FieldName nvarchar(50)

declare @SqlSort nvarchar(250)

declare @SqlCount nvarchar(2000)

declare @FromTemp nvarchar(1000)

declare @SqlResult nvarchar(4000)declare @PageMinBound int

declare @PageMaxBound int

--------首先生成排序方法---------

if @SortType='ASC' --升序 begin if not(@SortField is null) set @SqlSort = ' Order by ' + @SortField else set @SqlSort = ' Order by ' + @PK end

else --降序 begin if not(@SortField is null) set @SqlSort = ' Order by ' + @SortField + ' DESC' else set @SqlSort = ' Order by ' + @PK + ' DESC ' end

--------生成查询语句--------

if @Filter is null or @Filter='' --没有设置显示条件 begin set @FromTemp = ' From ' + @Tables end

else begin set @FromTemp = ' From ' + @Tables + ' where ' + @Filter end

set @SqlCount= 'select @RecordCounts=Count(' + @PK + ') '+@FromTemp

----取得查询结果总数量-----exec sp_executesql @SqlCount,N'@RecordCounts int out ',@RecordCounts out

--取得分页总数

if @RecordCounts<= @PageSizeset @PageCount = 1

elseset @PageCount = (@RecordCounts / @PageSize) + 1

if @PageIndex=1

begin --第一页用TOP方法来获取当前页记录 set @SqlResult='select top '+str(@PageSize)+' '+@Fields+' '+@FromTemp+' '+@SqlSort

end

else

begin --用临时表获取当前页计录 set @PageMinBound=(@Pageindex-1)*@Pagesize set @PageMaxBound=@PageMinBound+@Pagesize create table #Pageindex (id int identity(1,1) not null,nid int) set rowcount @PageMaxBound set @SqlResult='insert into #Pageindex(nid) select '+@PK+' '+@FromTemp+' '+@SqlSort set @SqlResult=@SqlResult+' select '+@Fields+' from '+@Tables+' a,#Pageindex p where a.'+@PK+'=p.nid and p.id>'+str(@PageMinBound)+' and p.id<='+str(@PageMaxBound)

end

print @SqlResultexec sp_executesql @SqlResult

GO