SQL 2000 Paging

Create PROCEDURE [dbo]. [Proc_getpagelist]

(
@Tables varchar (1000),--table name
@PK varchar (100),--primary key
@Fields varchar (1000) = ' * ',--the field of the query
@SortField varchar = NULL,--sorted fields
@SortType varchar = ' ASC ',--sort by
@PageIndex int = 1,--page index
@PageSize int = 10,--page size
@Filter varchar = NULL,--Filter condition
@PageCount int = 1 OUTPUT,----the total number of pages after the query results are paged
@RecordCounts int = 1 output----The number of records queried

)

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

--------first generate the Sort method---------

If @SortType = ' ASC '--Ascending
Begin
If not (@SortField is null)
Set @SqlSort = ' Order by ' + @SortField
Else
Set @SqlSort = ' Order by ' + @PK
End

Else--Descending
Begin
If not (@SortField is null)
Set @SqlSort = ' Order by ' + @SortField + ' DESC '
Else
Set @SqlSort = ' Order by ' + @PK + ' DESC '
End

--------Generate query Statements--------

If @Filter is null or @Filter = "--no display condition is set
Begin
Set @FromTemp = ' from ' + @Tables
End

Else
Begin
Set @FromTemp = ' from ' + @Tables + ' where ' + @Filter
End

Set @SqlCount = ' Select @RecordCounts =count (' + @PK + ') '[email protected]

----Get the total number of query results-----
EXEC sp_executesql @SqlCount, N ' @RecordCounts int out ', @RecordCounts out

--Get the total number of pages

If @RecordCounts <= @PageSize
Set @PageCount = 1

Else
Set @PageCount = (@RecordCounts/@PageSize) + 1

If @PageIndex =1

Begin--The first page uses the top method to get the current page record
Set @SqlResult = ' Select Top ' +str (@PageSize) + '[email protected]+ ' [emailprotected]+ '[email Protected]

End

Else

Begin-Get the current page count with a temporary table
Set @PageMinBound = (@Pageindex-1) * @Pagesize
Set @[email Protected][email protected]
CREATE TABLE #Pageindex (ID int identity () not Null,nid int)
SET ROWCOUNT @PageMaxBound
Set @SqlResult = ' INSERT INTO #Pageindex (NID) Select '[email protected]+ '[email protected]+ '[email protected]
Set @[email protected]+ ' SELECT '[email protected]+ ' from '[email protected]+ ' A, #Pageindex p
where A. '[email protected]+ ' =p.nid and p.id> ' +str (@PageMinBound) + ' and p.id<= ' +str (@PageMaxBound)

End

Print @SqlResult
EXEC sp_executesql @SqlResult

GO