分页报表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PSysPager_Rpt]   
@Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID   
@PK varchar(30), 
@countFields nvarchar(max),--必填！！第一行显示的字段 格式为：'' as StoreName,'' as UserName 字段跟@Filter一致 
@Fields nvarchar(max) = '*',--读取字段   
@Filter nvarchar(max) = NULL,--Where条件   
 @xwPageIndex int = 1,    --开始页码 
@xwPageSize int = 20     --行数 
 
AS   
 
--DECLARE @datasql nvarchar(max) 
--declare @countsql nvarchar(max)  
--declare @sql nvarchar(max) 
 
 
 
 
--计算总记录语句 
--set @countsql = 'select totcol, '+ @countFields + ',1 as cusorder from ( SELECT   COUNT(*) AS totcol  FROM  '+ @Tables+ @Filter + ') AS C'    
 
--查询数据语句 
if @xwPageIndex < 1 
 set @xwPageIndex = 1 
 
if @xwPageIndex = 1 --第一页查询 
 begin  
 DECLARE @xwPageSizeStr varchar(10) 
 set @xwPageSizeStr = str(@xwPageSize) 
EXEC('select totcol,' +  @Fields + ' from ('+ 
 'select totcol, '+ @countFields + ',1 as cusorder from ( SELECT   COUNT(*) AS totcol  FROM  '+ @Tables+ @Filter + ') AS C' +  
 ' union '+ 'select top ' + @xwPageSizeStr +' 0 as totcol, '+ @Fields + ', 2 as cusorder  from ' + @Tables+  @Filter +  
 ') AS E order by cusorder ' )   
--  set @datasql = 'select top ' + str(@xwPageSize) +' 0 as totcol, '+ @Fields + ', 2 as cusorder  from ' + @Tables+  @Filter  
 end 
else  -- 
 begin  
  DECLARE @START_ID varchar(50) 
  DECLARE @END_ID varchar(50) 
  SET @START_ID = convert(varchar(50),(@xwPageIndex  - 1) * @xwPageSize + 1) 
  SET @END_ID = convert(varchar(50),@xwPageIndex  * @xwPageSize ) 
--  set @datasql = ' SELECT 0 as totcol,' + @Fields + ', 2 as cusorder from (SELECT ROW_NUMBER() OVER(ORDER BY '+@PK+') AS rn, '+@Fields+ ' FROM  '  + @Tables+ @Filter+ ') AS D WHERE rn BETWEEN '+@START_ID+' AND ' +@END_ID  
 
  EXEC('select totcol,' +  @Fields + ' from ('+ 
  'select totcol, '+ @countFields + ',1 as cusorder from ( SELECT   COUNT(*) AS totcol  FROM  '+ @Tables+ @Filter + ') AS C' +  
  ' union '+ ' SELECT 0 as totcol,' + @Fields + ', 2 as cusorder from (SELECT ROW_NUMBER() OVER(ORDER BY '+@PK+') AS rn, '+@Fields+ ' FROM  '  + @Tables+ @Filter+ ') AS D WHERE rn BETWEEN '+@START_ID+' AND ' +@END_ID +  
  ') AS E order by cusorder ') 
  print(@Filter) 
 end