两个分页存储过程

drop proc [dbo].[UP_GetRecordByPage]
go
CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @strcontent   nvarchar(255),   -- 查询结果
    @PageSize     int ,           -- 页尺寸
    @PageIndex    int,            -- 页码   
    @order_by nvarchar(2000),    -- 排序字符串
    @strWhere     varchar(1000) ,   -- 查询条件 (注意: 不要加 where)
 @TotalRecords int output   -- 返回记录总数
AS

declare @alls int
SET NOCOUNT ON
declare @where_string nvarchar(1000)
declare @sql nvarchar(1200)
declare @pagecount int
declare @row_num int
if @strWhere=''
 begin
  set @where_string=@strWhere
 end
else
 begin
  set @where_string=' where '+@strWhere
  set @strWhere=' and '+@strWhere
 end
if not @order_by=''
 begin
  set @order_by=' order by '+@order_by
 end 
CREATE TABLE #pgs(id [nvarchar] (1),pgs [int])
--delete from pgs
begin
 set @sql=N'insert #pgs(pgs,id) select count(*) as pgs,''1'' as id from '+@tblName+@where_string
 exec sp_executesql @sql
 print @sql
  select @row_num=pgs from #pgs where id='1'
 select @TotalRecords=@row_num
 --select @TotalRecords=@SPintRootRecordCount
 if @row_num>@pagesize
    begin
   set @row_num=@PageIndex*@pagesize
   if @row_num=@pagesize
       begin
      set @sql=N'select top '+cast(@pagesize as nvarchar(100))+@strcontent+' from '+@tblName+@where_string+@order_by
      print @sql
      --exec sp_executesql @sql
    end
   else
    begin
      set @row_num=(@PageIndex-1)*@pagesize
      set @pagecount=@row_num
     set @sql=N'select top '+cast(@pagesize as nvarchar(100))+@strcontent+'  
     from '+@tblName+' where '+@fldName+' not in (select top '+cast(@pagecount as nvarchar(100))+@fldName+' from '+@tblName+@where_string+@order_by+') '+@strWhere+@order_by
     print @sql                                             
      exec sp_executesql @sql
    end
     end
 else
   set @sql='select '+@strcontent+' from '+@tblName+@where_string+@order_by
   exec sp_executesql @sql
end

drop proc Turnpage
go
CREATE PROC Turnpage
    @qCols varchar(200),                --需要查询的列
    @qTables         varchar(200),       --需要查询的表和条件
    @iKey         varchar (20),       --标识字段
    @oKey        varchar(20),        --排序字段
    @pageSize         int,                 --每页的行数
    @pageNumber       int                   --要显示的页码, 从开始
AS
set nocount on
BEGIN
    DECLARE @sqlText AS varchar(1000)
    DECLARE @sqlTable AS varchar(1000)
    SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @qCols +' from '+ @qTables + ' order by '+@oKey+ ''
   
 SET @sqlText =
        'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
        'FROM (' + @sqlTable + ') AS tableA ' +
        'WHERE ' + @iKey + ' NOT IN(SELECT TOP ' +
        CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @iKey +
        ' FROM (' + @sqlTable + ') AS tableB)'
  EXEC (@sqlText)
 --print(@sqltext)
END
GO