AspNetpager支持多表分页的存储过程

alter PROCEDURE [dbo].[pagemuchtable]

 @tblName   varchar(255),            -- 表名
 @ftblname varchar(255),    --从表
 @fstrfieldes varchar(1000),         -- 需要返回的列 
 @strGetFields varchar(1000),           --从表字段
 @fldName varchar(255)='',           -- 主键
 @fldnamefk varchar(255)='' ,         --外键
 @ftablenamepk varchar(255)='',       --从表主键
 @PageSize   int = 10,               -- 页尺寸
 @PageIndex  int = 1,                -- 页码 
 @strOrder varchar(1000) = '',      -- *****排序子句(注意: 不要加 order by)
 @strWhere  varchar(1500) = '' ,      -- 查询条件 (注意: 不要加 where)
             @Count int output                 -- 返回记录总数

AS
 declare @strSQL   varchar(5000)     -- 主语句

    if @strOrder != ''
  set @strOrder = ' order by ' + @strOrder
  
  
 if @PageIndex = 1
  begin
   if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) +' [' + @tblName + '].'+@strGetFields+ ',['+@ftblname+'].'+@fstrfieldes+'  from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' and  ' + @strWhere +' '+ @strOrder
   else
    set @strSQL = 'select top ' + str(@PageSize) +' [' + @tblName + '].'+@strGetFields+ ',['+@ftblname+'].'+@fstrfieldes+'  from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+''+ @strOrder     
   --如果是第一页就执行以上代码，这样会加快执行速度
  end
 else
  begin

  --以下代码赋予了@strSQL以真正执行的SQL代码
                                                                    
  set @strSQL = 'select top ' + str(@PageSize) +' [' + @tblName + '].'+@strGetFields+ ',['+@ftblname+'].'+@fstrfieldes+'  from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' and  [' + @fldName + '] not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' ' + @strOrder + ') '  + @strOrder
  if @strWhere != ''
   set @strSQL = 'select top ' + str(@PageSize) +' [' + @tblName + '].'+@strGetFields+ ',['+@ftblname+'].'+@fstrfieldes+'  from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' and  '  + @strWhere + ' and [' + @fldName + '] not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' and ' + @strWhere + ' ' + @strOrder + ') ' + @strOrder
  end
 

 exec (@strSQL)

 DECLARE @strSQLCount nvarchar(3000)
 if @strWhere != ''
  begin
   SET @strSQLCount = N'SET @Count=(SELECT count(*) from [' + @tblName + '],['+@ftblname+']'+' where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' and ' + @strWhere +')'
   EXEC sp_executesql @strSQLCount,N'@tblName nvarchar(255),@strWhere nvarchar(1500),@Count int output',@tblName,@strWhere,@Count output
  end  
 else
  begin
   SET @strSQLCount = N'SET @Count=(SELECT count(*) from [' + @tblName + '],['+@ftblname+'] where [' + @tblName + '].'+@fldnamefk+'=['+@ftblname+'].'+@ftablenamepk+' , )'
   EXEC sp_executesql @strSQLCount,N'@tblName nvarchar(255),@Count int output',@tblName,@Count output
  end
GO