高性能分页存储过程

select count(1) from dbo.GatherKeyWordInfo
dbo.p_PartPage 'GatherKeyWordInfo','GatherKeyWordID',5,1000,'','',''
exec [dbo].[p_PartPage] 'V_GatherKeyWordInfo','GatherKeyWordID','*',5,1000,'','newid()',''
select top 10 * from V_GatherKeyWordInfo





if(object_id('p_PartPage') is not null)
drop proc [dbo].[p_PartPage]
go
create PROCEDURE [dbo].[p_PartPage]
@tableName varchar(50),
@tableId varchar(50),
@pageIndex int,         -- 当前页索引
@pageSize int,            -- 每页显示多少条
@strWhere varchar(500), -- 查询条件
@order varchar(50),  -- 排序 如： id asc,time desc
@Fields varchar(1000) = '*'--读取字段
AS

DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @strWhere IS NOT NULL AND @strWhere != ''
  BEGIN
   SET @strFilter = ' WHERE ' + @strWhere + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ''
  END
if @orderCol = ''
  set @orderCol = @tableId

IF @pageIndex < 1
  SET @pageIndex = 1

if @pageIndex = 1 --第一页提高性能
begin
  set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @tableName + ' ' + @strFilter + ' ORDER BY  '+ @order
end
else
  begin
  /**//*Execute dynamic query*/    
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@pageIndex - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@pageIndex * @PageSize)
    set @sql =  ' SELECT '+@Fields+ '
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@orderCol+') AS rownum,
     '+@Fields+ '
      FROM '+@tableName+' ' +@strFilter+') AS D
   WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@order
  END
  set @sql = @sql + '; SELECT  Count(*) FROM ' + @tableName + @strFilter  
EXEC(@sql)
go

select * from sysobjects where name = 'p_PartPage'