create proc P_Page_Data
(
@tablename varchar(200) , --表名
@strGetFields varchar(500) = '*', --查询列名
@PageIndex int = 1 , --页码
@pageSize int = 20, --页面大小
@strWhere varchar(1000) = '', --查询条件
@strOrder varchar(100) = '', --排序列名
@intOrder bit = 1, --排序类型 1为升序
@CountAll bigint output --返回纪录总数
)
as
begin
declare @strSql varchar(2000) --查询语句
declare @strTemp varchar(1000) --临时变量
declare @strOrders varchar(100) --排序语句
declare @table varchar(100)
declare @SQL nvarchar(1000)
declare @R bigint
set @SQL= N'select @R=count(*) from '+convert(nvarchar(200),@TableName)+' where ' + convert(nvarchar(1000),@strWhere)
--SELECT @SQL;
exec SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
set @CountAll= @R
if @intOrder = 1
begin
--为1是升序
set @strTemp = '>(select max'
set @strOrders = ' order by '+@strOrder+' asc '
end
else
begin
--否则为降序
set @strTemp = '<(select min'
set @strOrders = ' order by '+@strOrder+' desc '
end
if @PageIndex =1 --第一页纪录
begin
if @strWhere = ''
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
end
else
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where ('+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
if @strWhere != ' '
begin
set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) and '+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end
