分页存储过程

    
Create PROCEDURE [dbo].[Proc_GetRecordByPage]          
(          
 @tblName      NVARCHAR(2000),       -- 表名          
 @fldName      varchar(4000)='*',       -- 表的字段  
 @groupBy     varchar(800)='',       -- 分组         
 @strWhere     varchar(8000) = '',  --查询条件          
 @OrderByFld   varchar(500),       --排序条件          
 @pageSize int = 10,               --每页显示的记录个数          
 @pageIndex int = 1,              --要显示那一页的记录          
 @Counts int = 0 OUTPUT            --查询到的记录数          
)          
AS   
PRINT   LEN(@strWhere)
PRINT @strWhere
DECLARE @pageUp INT           
DECLARE @pageDown INT          
DECLARE @EXECSQL NVARCHAR(4000)       
          
SET @pageDown = @pageSize * (@pageIndex - 1) + 1          
--当前页的最后一条记录RowID          
SET @pageUp = @pageSize * @pageIndex   
--if(@groupBy='') 
  begin
    SET @strWhere=' WHERE 1=1 ' +@strWhere    
  end 
--else 
--  begin      
--     SET @strWhere=' having 1=1 ' +@strWhere   
--  end    
SET @ExecSQL= 'SELECT * FROM(SELECT '+@FldName+ ',RowID = ROW_NUMBER() OVER ('+@orderByFld+')          
     FROM ' +@tblName+' '+ @strWhere+@groupBy+' )          
       AS PageTableList '
declare @Page nvarchar(100)
set @Page =' WHERE RowID  BETWEEN ' + STR(@pageDown) +' AND ' +STR(@pageUp)          
print @ExecSQL +@Page
--获得总记录数          
EXEC(@ExecSQL +@Page)          
SET  @ExecSQL='SELECT @Counts= COUNT(*) FROM ('+ @ExecSQL + ') As TotalCount '
exec sp_executesql   @ExecSQL,N'@Counts int output ',@Counts   output             
PRINT @Counts