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