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<= @PageSize
set @PageCount = 1

else
set @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 @SqlResult
exec sp_executesql @SqlResult

 

GO