CREATE proc dbo.CrmPageGetPagesByMoreField
@FieldList nvarchar( 3000 ), --取出的列
@TableName nvarchar( 1000 ), --表名
@Filter nvarchar( 3000 ), --搜索条件
@OrderList nvarchar( 3000 ), --排序字段 , 按主键顺序排列
@KeyName nvarchar( 200 ), --主键的字段名称
@CurrentPage int, --当前页,从0开始
@PageSize int, --每页的页数
@RecordCount int out, --总的记录数
@PageCount int out --总的页数
as
Declare @sqlStr nvarchar( 4000 ) --查询语句
Declare @strGetRecordCount nvarchar(4000)--得到记录数的语句
--如果没有指定@Filter , 用一个恒定表达式
if @Filter = ''
begin
set @Filter = ' 1 > 0 '
end
set @strGetRecordCount = 'select @RecordCount = count( distinct '+ @KeyName + ') from ' + @TableName + ' where ' + @Filter
exec sp_executesql @strGetRecordCount ,
N'@RecordCount int out',
@RecordCount out
if @PageSize = -1
begin
set @PageSize = @RecordCount
end
--取出总的页数
if @RecordCount !=0
begin
set @PageCount = ceiling( @RecordCount*1.0 / @PageSize )
end
else
begin
set @PageCount = 0
end
if @PageCount != 0
begin
if @CurrentPage >= @PageCount
begin
set @CurrentPage = @PageCount -1
end
end
--如果没有指定取出多少列,则取出所有的
if @FieldList = ''
begin
set @FieldList = ' * '
end
if @OrderList = ''
begin
set @OrderList = @KeyName + ' asc '
end
if @RecordCount > 0
begin
set @sqlStr = ' select top ' + Convert( nvarchar( 50 ) , @PageSize )+ @FieldList + ' from ' + @TableName
set @sqlStr = @sqlStr + ' where ' + @KeyName + ' not in ' + ' ( select top ' + Convert( nvarchar( 50 ) , @PageSize * @CurrentPage ) + @KeyName + ' from ' + @TableName + ' where ' + @Filter + ' order by ' + @OrderList + ' ) and ' + @Filter + ' order by ' + @OrderList
end
else
begin
set @sqlStr= ' select top ' + Convert( nvarchar( 50 ) , @PageSize )+ ' '+ @FieldList + ' from ' + @TableName + ' where ' + @Filter + ' order by ' + @OrderList
end
print @sqlStr
exec( @sqlStr )
GO