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