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