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
